Icon View Incident Report

Serious Serious
Reported By: Jeff Dunlop
Reported On: 12/9/2009
For: Version 2.03 Build 6
# 3117 Query Optimizer Making Bad Decisions with Certain Join Re-Orderings

SaleTransaction has 4mil rows, Sale has 2mil. TransactionTypeLookup has about 10 rows. If I remove the marked join, the query runs in 1.5 seconds and returns 2000 rows. If I leave the marked join in, the query takes unbelievably long (I've never actually got it to complete) and the server shows the CPU pegged, but very low disk access.

SELECT *
FROM SaleTransaction
INNER JOIN Sale ON SaleTransaction.SaleCode=Sale.SaleCode
INNER JOIN TransactionTypeLookup ON TransactionType=TransactionTypeLookup.TransactionType -- Problematic join
WHERE (SaleDate BETWEEN DATE '2008-12-01' AND DATE '2008-12-02' AND
SaleStatus = '2')



Comments Comments and Workarounds
The problem was with ElevateDB making a join re-ordering decision that resulted in worse performance than if the query was executed with the NOJOINOPTIMIZE clause, which suppresses join re-ordering.


Resolution Resolution
Fixed Problem on 12/10/2009 in version 2.03 build 7


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image