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

---

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

---

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