Kbase P106666: SQL: An SQL query reads some 44000 records to return a 4000 row result set.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/19/2008 |
|
Status: Verified
SYMPTOM(s):
SQL: Query generates a high number of record reads.
Promon shows 44000 record reads
The result set returned is only 4000 rows.
Query plan shows the nested table is scanned for each outer table.
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.1x
OpenEdge 10.1x
CAUSE:
The UPDATE STATISTICS was not executed against the database. The statistics generated by the UPDATE STATISTICS commands provide information to the SQL engine that describes the real characteristics of the database data - number of rows in a table, distribution of data, number of unique values in an index, etc.
This data enables the SQL optimizer to make much better decisions constructing the query plan for the query, and so get a better performing query plan. Sometimes the difference can be very dramatic as seems to be the case here.
FIX:
Optimize all SQL queries' performance by executing the UPDATE STATISTICS commands periodically. Executing UPDATE STATISTICS is the main standard advice for all SQL performance situations.
IN this case,the poor performance is directly attributable to the fact that UPDATE STATISTICS was not executed against the database. Running UPDATE STATISTICS yielded 1100% improvement in performance.
Specifically, before running UPDATE STATISTICS, the query required 44000 database record reads to return a result set of 3600 rows. After the execution of UPDATE STATISTICS, the same result set was returned and only 4000 database record reads were required.