We have SQL on our Data Warehouse written using SHOWCASE. The SQL could be better, it is not SO bad for something writen with a drag and drop gui, but the execution is far too slow - 12 minutes to extract data from the main 3.5 million record file, with several left outer joins.

So, we created indexes on the files, including one on a date ('yyyymm', character) field on the main file which was the primary WHERE selection - WHERE MYDATE = '201103'. According to performance information collected, the query used the index, which should have restricted the selection to roughly one-twelfth of the records. But there was no speed increase whatsoever, and it looked as if all records in the main file were still read.

Pool stats seem fine, DB faults in the run were 2.3 for 2721 pages. The 'classic' (slow) SQL engine is not being used, we have prohibited DDS-created logical files. Why is there no effect when valid indexes are created? How can we speed up the runs?

We do intend to look at flattening the data, splitting the big file, other physical rearrangements - but why are indexes not giving any improvement?



Software/Hardware used:
AS400, SHOWCASE