Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Poor response time when running a SQL select statement

I have a problem where I'm getting a significant variance in response time when running a SQL select statement over a view. The view is a join of 12 files where all of the joins are left outer. All of the selection criteria use fields from the primary file, and I have an EVI over the primary file that contains only the fields used for the selection clause. When I execute two select statements where the only difference between the two is the values of the fields in the selection clause, however, one statement consistently uses the access path created by the EVI in its execution plan, while the other one always does a table scan on the primary file. What is causing this?
The difference in behavior is due to the selection values causing the cost-based query optimizer to react differently to the similar select statements. Cost-based optimizers look at the selection field values passed in and make a determination on how many rows are going to be returned based on the selection values passed in. For example, the optimizer might determine that a selection value of 'A' will return 1000 rows and a selection value of 'Z' will return 1,000,000 rows - for the 'A' selection value the optimizer might choose an index scan (i.e., use the EVI) and use a table scan for the selection value of 'Z' since it's determined a large majority of rows in the able will be returned by 'Z'.

These query optimization are covered in detail in the iSeries SQL Performance workshop. Portions of this workshop can be accessed online.


Search400.com's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: tips, tutorials and more.

Check out this Search400.com Featured Topic: Database issues resolved

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.