Issue with processing time of JDBC receiver adapter

[Former Member]

Hi all,

We are using PI 7.1 EHP1.

We have an issue with JDBC receiver adapter taking too much time to process messages.

We are using XML SQL format message protocol with INSERT_UPDATE as the document format.

Each message can contain multiple records, i.e. 1 single message can result in many updates/inserts.

Currently, time taken to process in 6-12 secs which is quite high considering the messages are not very large in size.

We sent the statements to Oracle DBA to see if anything about the queries being used is causing issues. Awaiting inputs.

In the mean time, wanted to check if there is anything that can be done from PI side that will help us improving the processing time.

Thanks in Advance,

Sailaja.

[marksmyth]

Hello

You can use the tool in note #1514898 XPI Inspector for troubleshooting XI to trace the JDBC channel.

Regards

Mark

[Former Member]

Hello Sailaja,

Yes, JDBC receiver Channel has been advertising problems with bulk operations.

1.Could you go for increasing the JDBC Adapter threads in the Adapter Engine. Basis Team can do it for you.

By default the value is 5, you can go for 10 or 15, depending on the strenght of your Adapter Engine.

2. Check for fine tuning the DB

Regards

Jack Krugmann

[Former Member]

Hi Jack,

1) We have 10 JDBC threads currently available.

2) We removed all indexes on the tables and tested in a INSERT only, batch mode scenario as well, but no luck. Its still taking 10-11 seconds per record.

Can you please elaborate a bit on fine tuning the DB?

Thanks & Regards,

Sailaja.

[rajasekhar_reddy14]

Hi,

Shabarish pointed two valid points investigate on that,i

recommend to go for staging tables ,because directly inserting/updating data in to main table always risky (performance point of view) and let us know how many messages processing every day.

Regards,

Raj

[markangelo_dihiansan]

Hello,

Can you try using SQL_QUERY instead and let us know of the results?

http://help.sap.com/saphelp_nwpi711/helpdata/en/44/7c24a75cf83672e10000000a114a6b/frameset.htm

Hope this helps,

Mark

[Former Member]

Hi Mark,

We are basically using update as a lookup for existing records and doing an insert if update is not successful. Is there any way to use SQL_QUERY for this scenario?

Thanks & Regards,

Sailaja.

[Former Member]

Hi,

i think the main cause is query taking long time to execute in DataBase.

-> increase the read time and response time in the JDBC receiver adapter.

In the advanced mode table section of sender channel and receiver channel configurations, we can set driver properties for each DB connection. Any such property would have to contain prefix 'driver:'(with out quotes).

For Oracle Database JDBC thin driver 10.2.0.3 version, the property oracle.jdbc.ReadTimeout helps to set read timeout while reading from the socket. Also for setting login time out in Oracle, we use oracle.net.CONNECT_TIMEOUT. To set these two properties use as follows: driver:oracle.jdbc.ReadTimeout 1000 driver:oracle.net.CONNECT_TIMEOUT 1000 The TimeOut Driver properties like ReadTimeout and CONNECT_TIMEOUT are in milliseconds.

Refer note 1078420 for more details

please go through this blog i hope it will help you.

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/c059d583-a551-2c10-e095-eb5d95e03747

regards,

ganesh

Edited by: ganesh.nijampudi on Oct 25, 2011 12:00 PM

[Shabarish_Nair]

usually these issues could be attributed to;

1. Query taking long time to execute in the DB itself. DBA generally does an indexing of the queried table to optimize performance

2. Another cause is due to network latency.

I think if you can confirm on the above, you should be able to drill down to the issue

[Former Member]

Hi Vijay,

We even tried with an INSERT only, batch mode. But still a single record execution is taking 10-11 secs which is very long time. Is there any way to identify if it is a network issue?

Any pointers on increasing the logging level in the channel audit log will be helpful. I can get these settings done and try to figure out what is taking too long.

Thanks & Regards,

Sailaja.