CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Brian Webster
Brian Webster

Reputation: 30865

SQL Server 2008 R2 - Seemingly conflicting joins cause query to be very slow

This query takes 16 seconds to run

SELECT 
    WO.orderid
FROM 
    WebOrder as WO
    INNER JOIN Addresses AS A ON WO.AddressID = A.AddressID
    LEFT JOIN SalesOrders as SO on SO.SO_Number = WO.SalesOrderID

If I comment out either of the joins, it runs in a small fraction of a second. Example:

SELECT 
    WO.orderid
FROM 
    WebOrder as WO
    INNER JOIN Addresses AS A ON WO.AddressID = A.AddressID
    -- LEFT JOIN SalesOrders as SO on SO.SO_Number = WO.SalesOrderID

or

SELECT 
    WO.orderid
FROM 
    WebOrder as WO
    -- INNER JOIN Addresses AS A ON WO.AddressID = A.AddressID
    LEFT JOIN SalesOrders as SO on SO.SO_Number = WO.SalesOrderID

Notes

Execution Plan for the slow version (SalesOrders Join commented out)

enter image description here

Execution Plan for fast version

enter image description here



Why do these joins when used in conjunction with one another cause this to go from ~0.01 seconds to 16 seconds?

Upvotes: 1

Views: 466

Answers (1)

ta.speot.is
ta.speot.is

Reputation: 27214

Your execution plan doesn't show any expensive operations, I would try to following to troubleshoot bad performance:

  • Rebuild Indexes
  • Update Stats
  • DBCC FREEPROCCACHE

Personally I wouldn't expect the latter to do anything -- it looks like you have a sensible query plan as it is.

Upvotes: 2

Related Questions