Kbase P154207: SQL-92 query takes a very long time to complete using OE10.2A02.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/9/2010 |
|
Status: Unverified
SYMPTOM(s):
SQL-92 query takes a very long time to complete using OE10.2A02.
_sqlsrv2 process utilizes 100% of CPU.
When using OE10.1C and OE10.2A the same query almost immediately returns a result set.
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.2A02 32-bit Service Pack
OpenEdge 10.2A02 64-bit Service Pack
CAUSE:
The database did not contain SQL statistics. Before the Update Statistics was executed the query ran until it exhausted the lock table. In other words, the query was going to run a long time, and the query plan was building many dynamic indexes.
Dynamic indexes make sense with small amounts of data, not large amounts of data. For SQL-92 to know how much data there is, SQL statistics must be created.
In earlier releases (pre-OE10.2A02), the same or similar queries may have worked without a performance penalty because the SQL-92 engine did not as aggressively use dynamic indexes for low volume data. In newer releases, SQL-92 uses different strategies for low volume data situations.
FIX:
Run update (table/index/column) statistics on the database objects referenced in the SQL-92 query.