JDBC Lookup Suggestions!

[Former Member]

Hello,

Currently we have an synchronous JDBC interface which is used to lookup records from a third party system.

We have a total of 10 fields that needs to be retrieved from database and each field has different set of conditions. So, the interface was designed to fetch all records based on common set of conditions and further being filtered out using the mapping.

But we found out that it is becoming a big performance bottleneck where thousands of records are being pulled into just for filteration using the mapping.

We are now thinking about changing the interface for using individual JDBC lookup for each field.

These are my clarifications.

1. Does individual jdbc lookup better than traditional one or is it a performance bottleneck? Is it ok to do multiple calls for fewer records or single call for bulk records?

2. We are using 7.1.1 where we can only send key fields for using jdbc lookup. Is there any way where we can specify the actual sql using the jdbc lookup?

3. If there are any better alternatives for this approach let us know as we need to make sure it does not cause performance bottlenecks.

4. Does the jdbc lookup perform single call for entire context or individual calls for each context?

Regards

Anandh

[ambrish_mishra · Accepted solution]

Hi,

>>>>>>We have a total of 10 fields that needs to be retrieved from database and each field has different set of conditions.

Is the data fetched from same table ?

1. Does individual jdbc lookup better than traditional one or is it a performance bottleneck? Is it ok to do multiple calls for fewer records or single call for bulk records?

It is ok to do multiple calls for fewer records if it improves performance and caters to the requirment

2. We are using 7.1.1 where we can only send key fields for using jdbc lookup. Is there any way where we can specify the actual sql using the jdbc lookup?

Would need details of your scenario to answer this and other questions below.

3. If there are any better alternatives for this approach let us know as we need to make sure it does not cause performance bottlenecks.

4. Does the jdbc lookup perform single call for entire context or individual calls for each context?

I suggest your explain your scenario a bit.

Ambrish

[Former Member · Accepted solution]

Hi Ambarish,

All the fields are fetched from same database table only.

My scenario is once I need to fetch records from DB table and map it to idoc. So currently we request/response structure and pass it on to DB based on some key fields.

As this is becoming a performance issues, I am thinking of doing multiple calls to same table for different tables.

Just checking to know if there are any better alternatives.

Regards

Anandh.B

[ambrish_mishra · Accepted solution]

Hi Anandha,

If the performance issues are in PI, it is better to make multiple calls to DB than choke the system.

Obviously the process requirement should not have an impact.

Ambrish

[nabendu_sen]

Hi Anandh,

Couple of comments:

1. Does individual jdbc lookup better than traditional one or is it a performance bottleneck? Is it ok to do multiple calls for fewer records or single call for bulk records? -

Don't think multiple call is going to enhance the performance. Its also not a best design. If 2nd field value has dependency on the 1st field and like same for others, then keep the current design but make a Stored Procedure call instead JDBC lookup. SP should perform the filtration rather than bringing the whole bulk and perform that in Mapping.

2. We are using 7.1.1 where we can only send key fields for using jdbc lookup. Is there any way where we can specify the actual sql using the jdbc lookup?

Yes, you can do. But then you need to perform from UDF. Please check the below blog:

http://scn.sap.com/people/gabrielsagayaselvam.panneerselvam/blog/2011/05/05/jdbc-lookup-optimization

3. If there are any better alternatives for this approach let us know as we need to make sure it does not cause performance bottlenecks.

Think about Stored Procedure but discuss first with the Database team.

4. Does the jdbc lookup perform single call for entire context or individual calls for each context?

This is internally handled by SAP. Not sure. But if we check the JDBC Communication Channel, it shows called only once.

Regards,

Nabendu.