Receiver JDBC

[former_member189441]

Hi

I have proxy to JDBC scenario. Every time i am deleting the all entries in the DB1 and inserting the new data. this interface runs only once per day. Proxy is sending 10,000 records per message and totally it sends 10 messages per day to insert in DB (10,000*10 = 1,00,000 records).

PI is deleting the all existing entries first and inserting all new records. Means Delete message should go first and after that 10 insert messages.

It was working fine..But we are facing some sequencing issue occasionally. Delete message going as 2nd message instead of first message, due to which we are loosing some records(first 10,000).

as We can't use queue in the receiver JDBC, I have introduced the queue in the proxy. Now the sequencing issue resolved.

after that the requirement changed to send the same data to DB2. Means we need to insert same data (1,00,000 records) in to both DB1 and DB2.

I have used new business service for new DB2 and the scenario working fine.

My questions and problems:

-> If i send 3 messages from proxy, total 6 messages (for DB1 and DB2) are using same queue while inserting the data..so that it is taking very long time to insert. Message sequnce in Queue =  DB1-1,  DB2-1,  DB1-2 ,  DB2-2 ,  DB1-3 , DB2-3 ..Is there any way i can use different queue for different receivers in inbound side??

-> PI is taking 20min to insert 10,000 records( 1MB) ..so it will take more than 4 hours to insert 20 messages(DB1&DB2) ..I can't use stored procedures..and I have already set maximum concurrency 5 and enabled disconnect from DB in the receiver channel and I am using different channels for DB1 and DB2..
Why is it taking so much time to insert? is there anyway we can reduce this by doing any performance tuning?

Thanks,

Krishna

[former_member189441]

Hi All

Yes, It is not because of queue. But normally the delay is happening as i am trying to insert 10,000 queries separately ( I just repeated the access tag and i could see these 10,000 queries in log view) . After introducing the queue, the delay increased, as each message will take 20 min..( all messages used to execute parallelly)

is there anyway i can execute single query for 10,000 records like below.

Insert into tablename(EMP_NAME,EMP_ID) VALUES('J','1000'),('P',1001),('S','1002'),('G','1003')

Thanks,

Krishna

[Former Member]

Hi

Did you try batch mode?

[Former Member]

before jumping for performance enhancement , lets collect some figures

1) how much time it is taking if you submit 10000 or more records directly in DB via insert query...

2) is it showing any improvement if no. of records increased or decreased?

3) what is the processing time in ERP, PI and DB seprately

4) are you doing any heavy transformation in mapping??

5) are you doing full database table refresh or only for selected records ?

with this one more point, if you can seprate out two operation operations i.e. delete and insert...

[former_member189441]

1) how much time it is taking if you submit 10000 or more records directly in DB via insert query...

20min

2) is it showing any improvement if no. of records increased or decreased?

in our scenario each message from proxy contains excatly 10,000 records only

3) what is the processing time in ERP, PI and DB seprately

ERP and PI (integration engine) just few seconds

4) are you doing any heavy transformation in mapping??

one-one mapping

5) are you doing full database table refresh or only for selected records ?

As specified above, Full refresh

[Former Member]

by seeing your answer , my opinion is there is no much scope of timing improvement from 20 min, as most of the time taken is of DB itself (even after you submit it directly to DB)

in this case,only one option left i.e. seprate delete operation - move it to DB, schedule a job in DB for delete before insert from PI , match the frequency  and check the timings...

[rajasekhar_reddy14]

I dont think Queue causing performance issue, if you think so then for time being rmeove sequensing logic in Proxy and try.

You are performing delete and insert operations for 10000 records this is kind of high volume because JDBC adapter has to execute 20000 statements on Data base.

Increase maximum concurrency value to 17 for that you need to change JDBC receiver thread assign value to 20.

refer below blog.

http://scn.sap.com/community/pi-and-soa-middleware/blog/2011/01/26/tuning-the-pi-messaging-system-qu...#

If you want sequencing logic then i dont think you can use differet queues because queue name asisgnment comes from Proxy.

Regards,

Raj

[Former Member]

Hello Krish,

To get rid of sequencing issue with detele+ insert operation, you can go for UPDATE_INSERT operation instead of delete+insert subsequently.

Regards,

Ashish

[Former Member]

HI Krishna,

Please check this note: 831162and 1404778

Q: 31

Regards,

Beena