BusinessObjects Board

job performance issue

Hi Exerts,

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

Appreciate your help

Thanks and Regards,
Venki


ursfriend77 (BOB member since 2011-03-02)

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.


eganjp :us: (BOB member since 2007-09-12)

Hello,

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.

hope this also helps,

Andrés


aidelia :argentina: (BOB member since 2006-02-02)

Andres,

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.

  • E

eepjr24 :us: (BOB member since 2005-09-16)

Yeahhh…

Jim and Ernie means the same in Spanish :slight_smile:

Regards,

Andrés (which means “the one who confuses names”)


aidelia :argentina: (BOB member since 2006-02-02)

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.


eganjp :us: (BOB member since 2007-09-12)

The good part about developing ETL processes is there are no general recipes, just a bunch of general guidances and a lot of effort and fun.

And also a lot of concepts that could be cross applied to any good ETL tool, so is not so difficult to jump from one to another…

My two cents,

Andrés


aidelia :argentina: (BOB member since 2006-02-02)