Re:JDBC look up performance issue in PI7.3

[allamudi_loordh]

HI PI gurus,

I have a scenario where i need to fetch values from JDBC and do jdbc look up for one field before passing it to proxy. Source  side i am fetching 15000 records so totally i should call 15000 times jdbc channel for look up and get the value.So it is taking 1 hour time for this. 

So can any one suggest me how to reduce this.

Currently i am using UDF for fetching one value by sending one value at a time.So i need a solution which can send all 15000 values send to jdbc in one single call so that i should recieve 15000 return values in single response.

Query is like this

Select emp_id form employee where emp_id=emp_reference;

please give some light on me.

Regards,

Loordh.

[rajasekhar_reddy14]

Hi,

The design is not correct, you have to perfrom look up in one shot not 15000 times, you have to build logic to send all queue values(15000) in one JDBC call look up.

This kind of logic possible with RFC but not sure about JDBC.

Best Regards,

Raj

[baskar_gopalakrishnan2]

This design is not the better approach. You can do lookup for say 50 fields not for 15000 lookup in the mapping. Are you reading the oracle reference table or transaction table. If it is reference table, you can move that lookup fields data in a flat file or value mapping in abap side.  So that after passing the data to proxy, you can lookup there and do the further processing. Keeping large volume of lookups during mapping is not better design or approach.

You basically make 15000 calls in a single message during mapping. If the database end is not respond faster you will not even process the message.