Icon View Incident Report

Serious Serious
Reported By: Dominique Willems
Reported On: 8/29/2000
For: Version 2.03 Build 1
# 616 SQL Query Join Optimizer Not Being Thorough Enough With Join Re-Ordering

The join re-ordering for INNER JOINs is not working properly, causing SELECT queries with complex joins that don't have the smallest table as the driver table to take longer than needed.

SELECT D."Sampling Point Descr", D."Sampling Point Code", D1.
"User Eq ID", D1."Serial Number",
D1."Customer Part ID", D1."Lab Part ID", D1."Customer ID", D1.
"Default Report", D1."Last Oil Used
ID", D2."Eq Type Name",
 D3."Eq Model Denomination", D3."Eq Engine Size", D4.
"Manufacturer Name", D6."Customer
Identification",
 D5."Customer Name", D7."Customer Site Name", D7.Street, D7.City, D7.
ZIP, D7."Extra Line", D8."Oil
Name", D9."Contact Person"
FROM "SAMPLEPT.DB" D, "USEREQ.DB" D1, "EQTYPE.DB" D2, "EQMODESC.DB" 
D3, "EQMANUF.DB" D4,
"CUSTNAME.DB" D5, "CUSTOMER.DB" D6, "CUSTADDR.DB" D7, "OIL.DB" D8, 
"CONTACTS.DB" D9
WHERE
(D1."User Eq ID" = :UserEqID)
 AND (D1."Sampling Point ID" = D."Sampling Point ID")
 AND (D3."Eq Model Descr ID" = D."Eq Model ID")
 AND (D3."Eq Type ID" = D2."Eq Type ID")
 AND (D4."Eq Manufacturer ID" = D3."Eq Manuf ID")
 AND (D6."Cust ID" = D1."Customer ID")
 AND (D6.Name = D5."Customer Name ID")
 AND (D6.Address = D7."Customer Address ID")
 AND (D1."Last Oil Used ID" = D8."Oil ID")
 AND (D6."Contact Person" = D9."Contact Person ID")




Comments Comments and Workarounds
The workaround was to manually re-ordering the tables in the FROM clause.


Resolution Resolution
Fixed Problem on 8/31/2000 in version 2.04 build 1
Image