JDBC Performance Issue

[Former Member]

Hi All,

We are running a stress test on SAP(Proxy) to DB(JDBC) scenario. Sap is sending 1.5million records to Proxy in chunks of 100k messages. It's taking 5 hours to insert data in oracle database10g.

We are doing a simple insert using jdbc data type. Action = INSERT

Is there a way the performance can be enhanced?

Batch mode is checked in receiver jdbc adapter.

Key tags mandatory is checked.

Interpretation of Empty String Values = NULL Value

Persistance is Local

Conflict resolution = redo.

Transaction Isolation Level = Default

Disconnect from database after processing each message is checked

Database autocommit enabled is unchecked.

Kindly advice.

Xier

[agasthuri_doss]

Hi Ashish,

> Batch mode is checked in receiver jdbc adapter.

Good

>Disconnect from database after processing each message is checked

Good

>Database autocommit enabled is unchecked

Do it if needed.

>Proxy in chunks of 100k messages.

What is the time gap between each messages.

How many App server are available in the XI system, if you increase the App server it will tremendous change in Performance.

Regards

Agasthuri Doss

[Former Member]

Agasthuri could you tell me how to use the batch mode??

Xier

[agasthuri_doss]

Hi Ashish,

In Receiver JDBC Adapter, Go to Advance tab ---> Advance mode ( Check the Box )...where you will find Batch mode.

Regards

Agasthuri Doss

[ravi_raman2]

I agree turning of autocommit is a good thing..However its the only failover protection..in case of a connection Drop.......Question is is the performance drop that much that you have to turn it off...........

Regards

Ravi Raman

[Former Member]

but whats the difference between maximum concurrency and batch mode?

Regards,

Xier

[Former Member]

hi check the below links

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/61fd3e46-0a01-0010-d1bf-9d374fe9...

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/c50cddbb-0601-0010-92a7-f2a40ca6...

note:reward points if solution found helpfull.....

regards

chandrakanth.k

[justin_santhanam]

XIer,

I was seriously so much amazed of all ur questions buddy. Each and everytime you are asking you have to transfer 600 MB File, 1.5 Millions of records.. If you don't mind shall I ask something, is this is the daily volumes?

raj.

[Former Member]

Haha Raj, no these are month end volumes we are talking about...

Xier

[Former Member]

HI

look for JDBC interfaces that then spawn multiple messages with the following checkbox checked - Maintain Order at Runtime under quality of service.

Then queueing system behaves poorly and blocks other messages coming through (not sure which queues get blocked or whether it's all JAVA adapter engine queues).

OR

Pobably the problem lays somewhere on adapter engine.

There is a nice document on service marketplace about tunning. Maybe in your it was due to too less connection pool to database. It is always nice to look at:

http://<host>:<port>/MessagingSystem (it is adapter message monitor)

Please check also this:

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/70ada5ef-0201-0010-1f8b-c93...

/people/sap.user72/blog/2004/11/28/how-robust-is-sap-exchange-infrastructure-xi

This will help you,

http://help.sap.com/saphelp_nw04/helpdata/en/9e/6921e784677d4591053564a8b95e7d/frameset.htm

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/489f5844-0c01-0010-79be-acc...

Try performance tuning :

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/70ada5ef-0201-0010-1f8b-c93...

Thanks

Swarup

[Former Member]

I am not sure about the JDBC end but you can send a smaller chunk than 100k at a time. probably 50k or so. may be this might reduce some burden on JDBC