How to speed up AS/400 SQL?

2195 pts.
Tags:
AS/400 - DDS
AS/400 SQL
Data warehouse
DDS
Showcase
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following