BusinessObjects Board

call stored procedure from Data services

dataservices performance
#1

Hi,

I have simple stored procedure that selects records from table A(temp table)no indexes and insert into target table B(10 or more indexes). If i execute this stored procedure in sql developer using begin stp() end; it takes 25 seconds to load 200k records. However, same stored procedure called from data services using script sql(‘ds_name’, ‘stp’); it takes 16 minutes.

Every time the job is run, temp table is truncated and reloaded with new data. I use insert into insert /*+ append */ into B (a.b.c.d)
select a, b. c. d from A.

Also, if i call this stored procedure a test job it runs in 25 seconds, when it is made as part of other 200 DF, it takes 16 minutes.

Please advise why this is the case.

Smarty :us: (BOB member since 2008-05-12)

#2

have you tried it as

sql('DS','begin your_schema.your_procedure; end;')

You also mentioned putting it in a test job. Was it all on its own, or inside a workflow, or inside a dataflow?

And when you run it in with the other 200 dataflows is it inside one of the dataflows, or in a workflow between two dataflows, or in the job between two workflows?

Whenever I’ve had to run a procedure I put it inside a workflow but outside (usually between) dataflows. Not for any particular reason, just that it seems to fit better there for my logic.

thesnow :us: (BOB member since 2011-08-10)

#3

yes that is how I’m calling in stored procedure. sql(‘ds_name’, ‘begin; stp; end;’);

In my test job there is no WF and DF just script in test job and runs very fast like couple of seconds.

The same script, if I put it in the middle of 200 DF/WF within a job, it take 30 minutes.

Smarty :us: (BOB member since 2008-05-12)

#4

Not sure if this works, try gathering stats before you begin the SP.

Embed the code within SP in the beginning to gather stats.

execute dbms_stats.gather_table_stats(ownname => ‘Ownername’, tabname => ‘TABLE_NAME’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);

vinaykp (BOB member since 2010-07-15)

#5

Hello,

Just sharing some additional Information for all, I am currently using DS 4.2 Sp6 P2 and use the store procedure call all of the time.

Process is used to perform re-indexing to other tasks with MS SQL server.

Process is done in a daily basis for transnational updates and based on testing the run time are for the most part about the same from directly in SQL server and from DS.

There is somewhat a quicker response on the database side but that is expected.

Here is a example that is used from DS when our daily process is complete when reading and loading flat files into DS, the last process is a DS Script with the call from DS.

sql(‘Database_Server’, ‘EXEC Database_Name.dbo.sp_Name_of Procedure’);

Cheers!

c6907 :us: (BOB member since 2003-10-06)