Which one is best---Stored Procedure (or) Staging Server...??

[Former Member]

Hi All,

Our scenario is from Database -

---

> PI -

---

>SAP-ECC.

Data needs to be pulled from 10 to 15 tables that are located on DataBase.

So in order to pull the data from the Source DB we have the following approaches with us.

1) Using a Stored procedure to pull the data from DB.

2) Putting the data from the Source DB in form of files(not sure whether text or excel) onto a Staging Server and from that Staging server PI will pick the files by using File Adapter.

Can any one suggest me about the Pro's and Con's of the above approaches and which is the best one when it comes to performance.

Thanks,

Pruthvi

[former_member192238]

Hi,

Ask your DB taem to create staging table or View that contain all the tables structures if possible try this way

Regards

Praveen

[Former Member]

Hi Praveen,

Thanks for your suggestion but we want to use Staging Server only because by using that we can use File Adapter and I want to know if, performance wise, Stored Procedure is better (or) using File Adapter is better for this particular scenario.

Thanks,

Pruthvi

[former_member192238]

Hi,

Generally sender JDBC adopter has not great effficeicy for Stored Procedures but recently Michal has wrote a blog regarding multiple stored procedures

go through it hope this helps

Regards

Praveen

[Former Member]

Hi,

Although I would prefer Stored Procedures, there are some issues with Sender JDBC adapters in respect to stored procedures.

I am not sure with the staging server since development will be required at both ends. If xi can directly poll the data from any db, why would you require them to convert it into text files and further poll it upon using File communcation channel.

The only problem i see is that timing issue since you are retrieving more than 10-15 tables at a time.

With respect to performance, I would say not much of difference and purely depends upon the volume and frequency of the data you are retrieving.

Regards

Krish

Edited by: Krish on Oct 12, 2011 1:14 PM

[Former Member]

Hi Krish,

Thank you very much for your help.

In our scenario we want to pull the data of all the tables at one go i.e., data from all the tables will be collected at DB side and will be sent to PI either using Stored Procedures (or) putting data from all the tables in a single file on staging server.

Can you please let me know about the issues with Sender JDBC adapters in respect to stored procedures.

At this point of time we are not sure about the volume and frequency of data that will be retrieved.

However as an assumption, please let me know what approach needs to be followed if volume and frequency of data is high.

Thanks,

Pruthvi

[Former Member]

Hi,

This will give you more clarity.

just check this http://wiki.sdn.sap.com/wiki/display/XI/Advantages-DisadvantagesofStoredProcedureinJDBCScenario

I believe the issue was only with respect to oracle DB. Which Db are you using?

Regards

Krish

[Former Member]

Hi Krish,

We are using SQL DB.

Thanks,

Pruthvi