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

Register | Login 

Sunset Ride 
Sunset Ride 

General Notice: BOB is going to retire...please see details here. All BOB forums have been converted to "read only" as of September 1st.
General Notice: No events within the next 45 days.

Slow performance of the job


 
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
apoorv
Principal Member
Principal Member



Joined: 30 Aug 2005

Posts: 108
Location: Atlanta



PostPosted: Fri Jan 27, 2006 6:24 pm 
Post subject: Slow performance of the job

environment: SQL Server
DI version: 6.5

A particular job of mine used to take 5 minutes to process 1 miilion rows. since this morning the task is taking abnormally long time. after 1.5 hours only 17k rows were processed. The job is the same.
what could be wrong?
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3446
Location: Massachusetts, USA


flag
PostPosted: Sat Jan 28, 2006 8:49 am 
Post subject: Re: Slow performance of the job

There are way too many variables to give a good suggestion here. But I would first check the state of your SQL server and the source and target tables. Did anything change? Are you keeping up-to-date statistics?
Back to top
apoorv
Principal Member
Principal Member



Joined: 30 Aug 2005

Posts: 108
Location: Atlanta



PostPosted: Sat Jan 28, 2006 12:50 pm 
Post subject: Re: Slow performance of the job

Have done the following to try and solve the problem:
1.) The inputs have been uncached.
2.) The lookups have No_Cache.
3.) changed a three way join to a two way join using an intermediate template.
I am working in the Dev set up.

Have used outer joins in my data flows. does that have anything to do with it?
performance statistics: what is required?
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3446
Location: Massachusetts, USA


flag
PostPosted: Sat Jan 28, 2006 1:26 pm 
Post subject: Re: Slow performance of the job

I was thinking of database statistics -- that Oracle or SQL Server will keep for you, if you set them up to.

Can you monitor your source or target database servers during this process to see if it is the bottleneck?
Back to top
apoorv
Principal Member
Principal Member



Joined: 30 Aug 2005

Posts: 108
Location: Atlanta



PostPosted: Mon Jan 30, 2006 7:32 pm 
Post subject: Re: Slow performance of the job

Wanted to know the following:
1.) does use of outer joins impact the performance significantly?
2.) Is there a way in data Integrator to check any bottlenecks? say I have 4 lookups in a given transform. I want to know which transform is taking the maximum time and why? How can i check that aspect?
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10593
Location: Germany


flag
PostPosted: Tue Jan 31, 2006 3:53 am 
Post subject: Re: Slow performance of the job

regarding 1) yes, as another join strategy might be choosen (by DI if the join is executed in the engine or by the database is pushed down)
_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
eepjr24
Forum Enthusiast
Forum Enthusiast



Joined: 16 Sep 2005
ASUG Icon
Posts: 1815
Location: Orlando, FL


flag
PostPosted: Tue Jan 31, 2006 11:10 am 
Post subject: Re: Slow performance of the job

apoorv wrote:
Wanted to know the following:
1.) does use of outer joins impact the performance significantly?
2.) Is there a way in data Integrator to check any bottlenecks? say I have 4 lookups in a given transform. I want to know which transform is taking the maximum time and why? How can i check that aspect?


Process of elimination might be your only option. Run once with all in place. Check times from job stats. Remove one and retime. Wash, rinse, repeat.

- E
Back to top
developer_007
Forum Member
Forum Member



Joined: 11 Mar 2006

Posts: 3



PostPosted: Sat Mar 11, 2006 9:33 am 
Post subject: Re: Slow performance of the job

I've had the same problems. What I found was that the cause was either data driven or a process on the server which has caused everything else to slow down like someone querying that database with an inefficient query.

As for the data driven cause, that relates to the job maybe doing a lookup on data in a table where that data maybe missing. I had a job which looked up the currency rate for a specific currency of a certain day. If the rate for that day did not occur then the sql would loop so that the previous day was used and so on until a currency rate was found. Since the table had not been updated for a long time the sql looped conitinuously for every record I was processing causing the poor performance.

I suggest you run SQL profiler on the database while you run the job to see what is happening at a database level. Good luck!
Back to top
developer_007
Forum Member
Forum Member



Joined: 11 Mar 2006

Posts: 3



PostPosted: Sat Mar 11, 2006 9:35 am 
Post subject: Re: Slow performance of the job

I've had the same problems. What I found was that the cause was either data driven or a process on the server which has caused everything else to slow down like someone querying that database with an inefficient query.

As for the data driven cause, that relates to the job maybe doing a lookup on data in a table where that data maybe missing. I had a job which looked up the currency rate for a specific currency of a certain day. If the rate for that day did not occur then the sql would loop so that the previous day was used and so on until a currency rate was found. Since the table had not been updated for a long time the sql looped conitinuously for every record I was processing causing the poor performance.

I suggest you run SQL profiler on the database while you run the job to see what is happening at a database level. Good luck!
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 

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0293 seconds using 17 queries. (SQL 0.0030 Parse 0.0009 Other 0.0254)
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