Kbase P19811: Poor performance when running a multi-level query using ODBC / MS SQL DataServer
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
21/02/2006 |
|
Status: Verified
FACT(s) (Environment):
Progress 9.1x
ODBC DataServer
MS SQL DataServer
MS SQL
Windows NT 32 Intel/Windows 2000
SYMPTOM(s):
Poor performance when running a multi-level query using ODBC / MS SQL DataServer
Performance degrades when running a multi-level query
Multi-level query that shows poor performance:
FOR EACH customer NO-LOCK,
EACH order OF customer NO-LOCK,
EACH orderline OF order NO-LOCK,
ITEM OF orderline NO-LOCK:
END.
No performance degradation with Progress db
No performance degradation with ORACLE DataServer
The same query runs in 4 seconds when using Progress database, 9 seconds
with DataServer Oracle and 133 seconds using MS SQL Server DataServer.
CAUSE:
Bug# 20030327-012
CAUSE:
This performance issue is related to JOINs that is not being resolved by the server. Due some semantical differences allowed by 4GL that can be used by either dynamic or static queries, by omitting the EACH option or using FIRST or LAST, causes the DataServer to resolve the JOIN on the client side resulting in poor performance.
FIX:
Upgrade to 10.1A.
Alternatively if upgrade is not viable, as a workaround, place an EACH statement before the Item field as follow:
FOR EACH customer NO-LOCK,
EACH order OF customer NO-LOCK,
EACH orderline OF order NO-LOCK,
EACH item OF orderline NO-LOCK:
END.
This 4GL code change will generate a join by MSS SQL Server.