Director job indexing

benghiat99 · Post by **benghiat99** » Wed May 21, 2008 12:26 pm

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

chulett · Post by **chulett** » Wed May 21, 2008 12:34 pm

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.

ray.wurlod · Post by **ray.wurlod** » Wed May 21, 2008 4:01 pm

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.

chulett · Post by **chulett** » Wed May 21, 2008 5:45 pm

I'll do this to - but only with a Filter of some kind enabled... never for everything in the Project.

ray.wurlod · Post by **ray.wurlod** » Wed May 21, 2008 11:25 pm

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.

ArndW · Post by **ArndW** » Thu May 22, 2008 3:12 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu May 22, 2008 5:51 am

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.

ArndW · Post by **ArndW** » Thu May 22, 2008 5:58 am

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.