Predicting Bad Query Performance

Hey, Ted:

Occasionally a new query brings my shop's system to its knees. How do I deal with these problems?

-- Ben

Use the predictive query governor, Ben. Before running a new query (i.e., Query/400, SQL, OPNQRYF, a third-party query product), issue the Change Query Attributes (CHGQRYA) command, specifying a time limit of zero.

CHGQRYA QRYTIMLMT(0)

The time limit is the number of seconds you're willing to let a query run. It can be set to *NOMAX or any non-negative number up 2,147,352, 578, which gives you about 68 years. So, for example, if you don't mind a query running, provided it doesn't take over 12 years to complete, set the time limit to 378691200.

When you set the time limit to zero, the system does not run the query, but it will tell you how it planned to run the query.

Next, run the Start Debug (STRDBG) command to start the debugger. The query optimizer adds helpful messages to the job log when running queries in debug mode.

Run the query. You will receive a message that states, "Estimated query processing time xxx exceeds limit 0 (C I)". Enter the letter C if the time is unacceptable and look at the messages in the job log (DSPJOBLOG). If certain access paths would help your query run better, the messages will tell you so. You can then create those access paths before putting the query into production.

-- Ted

Sponsored By WORKSRIGHT SOFTWARE

CASS Certification What is it? Why do you need it? CASS stands for "Coding Accuracy Support System." This test, developed by the U.S. Postal Service, determines whether ZIP Code software can accurately assign ZIP Codes to mailing addresses. WorksRight offers CASS-certified software intended to improve the accuracy of carrier route, 5-digit ZIP, ZIP+4, and delivery point codes. For more information or a 30-day free trial, visit www.worksright.com or call 601-856-8337.