Re: AW: Index advisor and system cross-reference file

Hi,

it's nice that the query engine is smart enough to advise indexes we should create, but there are some buts:
- but this are only advices, I could follow, but I could decide not to do so
- but it could be better not to create the index, because it doesn't help (quey engine is not taking the index)
- but it could be better not to create the index, because it takes more ressources than it helps
- but it could be better not to create the index, because the table is written very often and ued very seldom in this way
- but it could be better not to create the index, because having too many indexes for one table could bring down performance
- but why should the query engine write down this advices, if nobody is interested in?
- but why is there no easy way to stop it from writing down the advices?
- but why is it taking so much ressources to write down the advices (this could be a bug!!!)

in my experience, and I was working in germanys biggest db2/400 installation as second level consultant for years (we had tables with > 900 million records, queried by micro strategy with fully by the fly generated complex sql statements), the only way to adjust index design was to have a look at reports, that didn't meet the performance requirements, and to measure the load process by STRDBMON and analyzing the output of dbmon. Using the gloobal index advisory file is absolutely useless for this, because you would simply not find, what you are looking for, it's not the table, it's the job or a report, you are interested in!!!

If the OP complains, that writing the index advices is on the top ten list with most ressources, I don't see that his application has to be optimized! An application has to be optimized, when performance requirements are not met, users are complaining, or the hardware budget is not high enough to take a bigger iron, or there is no bigger iron, or optimization would be cheaper than the bigger iron. The OP might be misinterpreting some measurements, but I didn't find any hints for this (maybe I missed this, because I didn't read all postings)

Dieter

PS: Using micro strategy for the booking system, you could customize the way its pulling the data and for DB2/400 it would be best to let ms pull subsets of data into temporary tables and do the final joining and sorting with these temp tables - this is very fast with parallel database feature and joining some tables with < 1000 records is taking some milliseconds, while joining some tables with hundreds of millions of records could take minutes, if the query engine is failing to find the best strategy (and the query engine won't find the best way in every constellation - it's software!!!).