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.

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