Icon View Incident Report

Serious Serious
Reported By: John Hay
Reported On: 10/5/2004
For: Version 4.12 Build 1
# 1865 SQL Joins Not Taking into Account Optimization Costs When They Should Be

I am having a performance problem with the following query. This query takes approximately 15-20 times as long as the revised query below it. With the dataset I am using, which has only one store, the same number of records are returned for both queries. There are separate indexes for ScanCode (Char(13)) and Store (Integer).

Slow:

SELECT * FROM Sales
JOIN StoreHeader on Sales.ScanCode=StoreHeader.ScanCode AND Sales.Store =
StoreHeader.Store

Fast:

SELECT * FROM Sales
JOIN StoreHeader on Sales.ScanCode=StoreHeader.ScanCode



Comments Comments
In the above join, the join should have been rewritten to avoid evaluating the Store condition until after the ScanCode condition was evaluated because the ScanCode condition was much more selective. The optimization costs weren't evaluated, however, so the engine didn't know that it should rewrite the join.

The joins now use the optimization costs to determine the optimal execution path and join order. In addition, the query plans now contain the estimated cost for each join.


Resolution Resolution
Fixed Problem on 10/10/2004 in version 4.13 build 1
Image