When to go for JDBC Stored Procedure - Pros and Cons

[Former Member]

Hi Experts,

To integrate PI with database through JDBC adaper, what is the best approach and when should we go for Stored procedure? What all factors need to be considered while deciding to go for Stored procedure or normal Select and update statement. In my case i have a scenario where i need to pick data periodically from 7 different tables and around 90 fields and need to update the tables for the records which has already been processed what should be the best approach for this integration.

Thanks

Sumit

[Former Member · Accepted solution]

Hi,

when should we go for Stored procedure?

Stored procedures are like functions that we generally use in other coding languages. With in stored procedure we provide some logic/steps to querry the DB system. The general approach to use SP is when your querries are really complex or multiline querries / querying multiple tables.

i need to pick data periodically from 7 different tables and around 90 fields and need to update the tables for the records which has already been processed what should be the best approach for this integration.

In this case you can have two approaches:

1. Ask your DB team to generate a staging table at the DB end it self and then you can write a query in the JDBC adapter to fetch the records and update with a flag.

2. The above is generally avoided by the DB team, so you can better ask them to develop a stored procedure which you can Execute from the Select query in your JDBC adapter. The update flag will also be done by the SP only. In your update field in JDBC channel u just need to give <Test> as a value.

Thanks,

Rahul Grover

[Former Member · Accepted solution]

Hi Rahul,

Thanks for reply, we will be having staging tables. DB team is planning to generate all the 7 tables as staging tables and then PI will be accessing these 7 tables to fetch the data and update the records to mark as read.

In this scenario what would be the best approach, do i need to ask them to create a stored procedure instead of creating 7 staging tables and what would be the pros and cons of this strategy.

For this scenario i can think of two approaches:

one is configure sender JDBC communication channel and provide single Select statements for 7 different tables using a join and update the header table to mark the records as read.

second ask them to create a stored procedure at the staging area and then i can call that SP from PI to get the desired data and update SP to mark the records as read or they can write a logic to update the tables in SP itself.

For the first approach i am worried about the performance as the data load is high and select statement will contain 7 different joins for upto 90 fields to be fetched.

Also if you have the better approach to create this scenario then your comments are most welcome.

Thanks

Sumit

[rajasekhar_reddy14 · Accepted solution]

Hi,

if your DB team create one staging table for all 7 Tbales then you dont required joins/SP's and this is good design too.

elase you have go with your approches what you have mentioned valid, but you want make sure that realtionship between all table should be correct and updation of flag should done accordingly, most of the times we face trouble here.

Regards,

Raj

[baskar_gopalakrishnan2]

> what is the best approach and when should we go for Stored procedure?

Benefits of stored procedures.

u2022Precompiled execution. DB Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

u2022Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

u2022Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.

u2022Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

> What all factors need to be considered while deciding to go for Stored procedure or normal Select and update statement?

a) if you are going for query to fetch bulk volume of data and frequency of reading the data is quite often .. say every few mins..

or

b) If you are not able to create normal statement/update query due to too many table joins or getting complex query then go for

Stored procedure.

All other instances go for normal Statement /Update join sql query.

Hope I answered your question.

[Former Member]

Hi,

I need one more confirmation if there is any possibility to get the result from Stored Procedure in a heirarchical structure like one header multiple line items and coresponding multiple sub line items to map the structure with a standard BAPI.

As far as I know and experienced, it is not possible to get the result in hierarchical format. we need to do some workaround in mapping to convert the resulted structure which will be always in a single heirarchy as a row into another heirarchical structure.

Please correct me if I am wrong and let me know if there is any such possibility or best approach to map the result to a standard bapi.

As per my knowledge best approach is to pass resulted structure from database to ABAP proxy and do the looping on structure to create seperate internal tables for header and line items according to BAPI structure and pass the data to BAPI.

Regards

Sumit

[baskar_gopalakrishnan2]

>>> As far as I know and experienced, it is not possible to get the result in hierarchical format. we need to do some workaround in mapping to convert the resulted structure which will be always in a single heirarchy as a row into another heirarchical structure.

You are right.

>>> As per my knowledge best approach is to pass resulted structure from database to ABAP proxy and do the looping on structure to create seperate internal tables for header and line items according to BAPI structure and pass the data to BAPI.

Answer: Use ABAP Proxy and map your result query data into proxy fields and in your abap coding level use some array or collection or List object to parse the data and assign the individual records accordingly to the backend system.

I think your approach will be efficient.

Hope I answered your question.

[stefan_grube]

> As per my knowledge best approach is to pass resulted structure from database to ABAP proxy and do the looping on structure to create seperate internal tables for header and line items according to BAPI structure and pass the data to BAPI.

As you must not - in no circumstances - never - use a BAPI in an async scenario, this is the only possible solution.

[Former Member]

Hi Everyone,

Thanks for your replies on my query, sometimes its really hard to justify the things to client even if you know the best possible solution but don't have any evidence to prove the things and specially in a situation where multiple teams for multiple systems are involved. everyone have their own opinion and things are getting complex. At that point i rely on SDN to get a supporting hand and as always i get full support from the experts.

Thanks

Sumit