Update_insert in JDBC Recevier Channel

[allamudi_loordh]

Hi Team,

i have a scenario proxy to jdbc. I want insert/update JDBC based on the account number existance. so when i am trying for 500 records itself it is taking 2 min time which is very high what i believe. i am usuing maximum cuncurrency as 3 and tables at sql server also indexed.

can any one please post me the strcture how it should be for improving performance.

as per sap below is strcture format for UPDATE_INSERT. my question is where should be 0..unbound either StatementName1 or access?

<root>

<StatementName1>

<dbTableName action=”UPDATE” | “UPDATE_INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2new</col2>

</access>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

</StatementName1>

[former_member190293]

Hi Allamudi!

Please check if this approach helps:

https://blogs.sap.com/2016/09/30/using-recordset-as-input-parameter-for-ms-sql-server-stored-procedu...

Regards, Evgeniy.

[allamudi_loordh]

Hi Evgeniy,

thanks for your reply but i am not using stored procedure here. So looking for options in xml sql format itself.

regards,

Loordh.

[former_member190293]

Hi Allamudi!

The point is that you can use one stored procedure call for performing update operation in DB instead of using multiple statements. You just need to create very simple SP in target DB yourself or ask your DB team for assistance.

I saw comments where people said that using this approach reduced insert operation time from 20 min to less than a minute on the huge amount of records.

Regards, Evgeniy.

[allamudi_loordh]

thanks Evgeniy,

i will check with my Legacy team on this approach and try it.

Regards,

loordh.

[former_member182412]

Hi Allamudi,

The sap help says access should be one for update_insert. Check below link.

Defining an UPDATE_INSERT Statement

Enter the new column values in the <access> element.
Enter exactly one <access>element.

Regards,

Praveen.

[allamudi_loordh]

Thanks Praveen for reply.

But when i run 500 records so 500 statements will be executed in parallel right so think of in Production i may expect 20000 records which means 20000 statements/times the query run at SQL server which will take more time. can you please let me know do you know any options how to increase the performance.

Regards,

Loordh.