If you use CHGQRYA to increase the limit and allow the statement to run, are
you really willing to wait 50 hours for it to complete?
It is quite possible that estimates are off and statement won't actually
take that long to run, but there is no guarantee it won't.
Just the fact that query optimizer estimated that high tells me you're
probably missing some key indexes over the 3 files in question, possibly
even missing join field keys (that's an absolute NO-NO, as you're then stuck
with Cartesian Product join and possibility of infamous "query that never
ends").
While i5/OS has autonomic statistics collector, best source of statistics
will always be abundance of good indexes (be it keyed LFs, keyed PFs, keyed
constraints or SQL indexes). These are maintained real-time (*IMMED) and
always provide up to date and accurate statistics. More importantly, they
provide query optimizer with alternative query implementation (i.e. keyed
access vs table scan).
Bottom line is you really should do some performance tuning and build
appropriate indexes. Since you've narrowed this task down to a single
query, your best choice is Visual Explain. It is in iNav's Run SQL Scripts
facility, see this thread on details how to get to it:
http://archive.midrange.com/midrange-l/200709/msg01200.html
If you build those "perfect" indexes, estimates will go down under your
25000 second limit and more importantly, query will perform in an acceptable
manner.
Elvis
Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400
http://centerfieldtechnology.com/training.asp
-----Original Message-----
Subject: CPF427F - Estimated Query Processing Time Exceeds Limit
I am working with a client who recently upgraded their ERP system.
An SQLRPGLE report program is getting error CPF427F - Estimated query
processing time exceeds limit.
The program is selecting records from three files (header has 11
million, detail has 44 million, third file (master) has 37k). The error
recommends doing CHGQRYA (default is 25000 seconds, estimated is
181,122). The SQL statements are variable built based on selection
criteria.
System is at V5R3.
What should I read performance improvement?
Thank you,
Bonnie Lokenvitz
midrange.com
