I have job scheduled everyday. 2 weeks the job was taken 2 hr 30 mins to execute job. since 2 weeks the job has been taking 3 hr 30 mins to load same data.
Please advise me how to improve performance of my job to decrease the time back to 2 hr 30 mins. And let me know what are best options to optimize the performance in BODS 4.2
The easy way? Hire somebody that is an expert at performance tuning.
The hard way? Examine the monitor log and look for items in it that are taking a long time. Find the objects that take the longest time and start there.
Adding up to Ernie’s thoughts, when you got that information, look for the task that have increased the most since the beggining, either by percentage or by absolute time. Usually there be one, two or very few DFs that should be reworked. Sometimes the problem is just a target table heavelly indexed and naturally with more rows since each job run.
But, if the increase is distributed evenly in all the job, then the problem could be just the amount of rows processed are increasing for an external cause.
Although Jim and I have both been on here forever, we are different people. =P
I agree with the above, though. Oft times your culprit will be some sort of DB or other external change when you get a sudden jump. Examining the logs can give you an idea of where to look. That all assumes it is not something obvious like a row count increase. Possible suspects are network bandwidth, hardware threshold exceeded (disk thrashing, memory swapped to disk, cpu pegged, etc), indices (dropped, added, not rebuilt), data complexity increase, DB volume fragmentation, etc, etc, etc.
If you aren’t comfortable digging in, call an expert, there are plenty available in the market.
Andrés, no big deal. I’ve been doing performance tuning for a long time. I have a number of tools I’ve developed over the years to help me find the objects that need the most attention. I consider Ernie to be a peer, so any answer from him is just as good as one from me.
It’s a lot easier to discuss specifics rather than generalities. One very specific tip I give to my developers is to always look at the Display Optimized SQL before you consider a Dataflow “done”. If there are multiple queries being executed by the Dataflow then that is usually a red flag. A Dataflow with a Table Comparison that has two queries in the Display Optimized SQL is OK.
Usually, the fewer queries (ideally, only one) a Dataflow submits the faster it executes.