BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

call stored procedure from Data services


 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
Smarty
Forum Member
Forum Member



Joined: 12 May 2008

Posts: 23


flag
PostPosted: Thu Oct 08, 2015 10:55 pm 
Post subject: call stored procedure from Data services

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.
Back to top
thesnow
Principal Member
Principal Member



Joined: 10 Aug 2011

Posts: 117
Location: Minneapolis, MN


flag
PostPosted: Fri Oct 09, 2015 2:49 pm 
Post subject: Re: call stored procedure from Data services

have you tried it as
Code:
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.
Back to top
Smarty
Forum Member
Forum Member



Joined: 12 May 2008

Posts: 23


flag
PostPosted: Fri Oct 09, 2015 3:00 pm 
Post subject: Re: call stored procedure from Data services

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.
Back to top
vinaykp
Senior Member
Senior Member



Joined: 15 Jul 2010

Posts: 31



PostPosted: Thu Oct 15, 2015 12:50 pm 
Post subject: Re: call stored procedure from Data services

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);
Back to top
c6907
Forum Member
Forum Member



Joined: 06 Oct 2003
ASUG Icon
Posts: 13


flag
PostPosted: Thu Sep 22, 2016 9:27 am 
Post subject: Re: call stored procedure from Data services

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!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0273 seconds using 17 queries. (SQL 0.0026 Parse 0.0009 Other 0.0238)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo