Page 1 of 1
Director job indexing
Posted: Wed May 21, 2008 12:26 pm
by benghiat99
Hello,
We have some projects with over 1,000 jobs. If you go into Directory with categories turned off it can take up to 3 minutes for Directory to display the job information. Does anyone know if there is a way of getting Director to use an INDEX to speed up this process.
Thanks and Regards,
Steve Benghiat
Posted: Wed May 21, 2008 12:34 pm
by chulett
No such thing, really. Curious, though, why you would be turning off categories in the Director? If there is a particular reason you are doing that (rather than just being a glutton for punishment) we should be able to help you find an alternate approach.
Posted: Wed May 21, 2008 4:01 pm
by ray.wurlod
I do this sometimes to sort by Status and bring all the Aborted jobs to the top (or all Running jobs).
Then I re-enable Categories.
Posted: Wed May 21, 2008 5:45 pm
by chulett
I'll do this to - but only with a Filter of some kind enabled... never for everything in the Project.
Posted: Wed May 21, 2008 11:25 pm
by ray.wurlod
I, on the other hand, adopt the approach of not having too many jobs in the project. Therefore "all jobs" still refreshes in a reasonable amount of time.
Posted: Thu May 22, 2008 3:12 am
by ArndW
I had a similar case a while ago where the project had so many entries that, even with categories, the refresh would take minutes. I traced all the calls that I could find and added a number of column indices (I think at one point I added a secondary index for every column in the main tables) and could never get the refresh to go appreciably faster.
Posted: Thu May 22, 2008 5:51 am
by ray.wurlod
Of course not. The query optimizer will disable the use of indices since a full table scan is required anyway (assuming no Filter is applied). Using indices in this case would only increase the I/O cost.
Actually an index on NAME may help if the current setting is sort by name.
Posted: Thu May 22, 2008 5:58 am
by ArndW
The refresh of the director log turns out to be coded directly and doesn't use the UV query mechanism at all, so the indices aren't used and there is no query optimizer involved either. It basic does a SELECT...
The abyssmal performance of the director with lots of objects has been an enhancement GTAR since Version 1.0 of DataStage and it has never been successfully adressed. I haven't looked at a large project at Version 8.x yet, but I suspect that nothing has changed there either.