JDBC adapter taking too much time for inserting

[Former Member]

we` have given a "update insert" query to be used in the JDBC adapter to insert these records into the database. Consists of 3 tables with 29fields in first, 4 fields in the other two.

While message in XI gets processed in just 1-3 secs, the database processing time goes up to as much as 8 minutes for one record to get inserted.

As immediate solution, is there any way we can have the JDBC adapter process these messages faster? These messages get queued up and hence all the other messages also get queued up delaying the other interfaces. We have a central adapter engine...

Also is there any way we can get alert when the status is in "Processing/To be delivered/Delivering" and the message count exceeds a certain number say 1000

[VijayKonam · Accepted solution]

Check if you have ticked the disconnect from database for each message. Uncheck it. It has highly adverse effect on performance.

VJ

[Former Member]

<<<< Also is there any way we can get alert when the status is in "Processing/To be delivered/Delivering" and the message count exceeds a certain number say 1000

You need to develop the program around the field "MSGSTAT " of Table "SXMSPMAST" & within this you can call the Alert. I know this is complicated but can be done.

- lalit -

• Reward Points if this suggestion is useful ***

[Former Member]

Lalit Chaudhary,

Thanks a lot for your response.Could u please explain in detail?

All,

would you please suggest some ways to reduce JDBC processing time as per this thread?.

[Former Member]

Rajesh

You can count the count of messges from table when MSGSTAT equals to " Being Processed " then you from that code itslef you can call the Alert.

Example for calling an alert from report -->

http://help.sap.com/saphelp_nw04/helpdata/en/d0/5e073c8e56f658e10000000a114084/content.htm

Lalit

[Former Member]

Hi,

What is SP level of XI ur using ?

Check this note :

Note 940291 - XI JDBC Adapter: Slow processing for large XML documents

Regards

Gangaprasad

[Former Member]

We are using SP17.and also it is SQL server.and also it we are inserting four diffrenet tables.it is not a large XML document

[prateek]

Ur database structure also could affect the processing speed. Are u using multiple channels or in a single channel u are using multiple Statement tags

U may also check that the database at receiver has enough memory space.

Regards,

Prateek

[Former Member]

I am using only one receiver JDBC channel

We have been inserting three different table by using 3 different statemets tags(i.e) statement1(for table1),statement2(for table2),statement3(for table3).

My structure is,

<messagetype Name>

<Statement1>

<tag1>

<action>UPDATE_INSERT</action>

<table>Table1</table>

<access>

<field1>

<field2>

......

......

<field28>

<key>

<MatNumber>

</tag1>

</statement1>

<Statement2>

<tag1>

<action>UPDATE_INSERT</action>

<table>Table2</table>

<access>

<field1>

<field2>

......

......

<field4>

<key>

<MatNumber>

</tag1>

</statement2>

<Statement3>

<tag3>

<action>UPDATE_INSERT</action>

<table>Table3</table>

<access>

<field1>

<field2>

......

......

<field4>

<key>

<MatNumber>

</tag3>

</statement3>

You can see we are also using key as well.In the first table we have 28 fields,second & third we are having 4.

Edited by: rajesh shanmugasundaram on Jul 31, 2008 11:08 AM

[Former Member]

Hi Rajesh,

Please ensure that you have checked batch mode in your receiver communication channel.

Also you can set the below in the advanced parameters.Committing transactions is extremely disk I/O intensive and slow. You should always turn auto-commit off.

WSConnection.set-AutoCommit (false).

-regards,

Shamly.