Poor clustered index seek performance?

Question

I've got these two queries:

SELECT SELECT NamesRecord.NameID, NamesRecord.FulfillmentAddressID NameFulfillmentAddressID, ContractRecord.FulfillmentAddressID, ContractRecord.BillingAddressId
FROM Magnet.dbo.ContractRecord ContractRecord
 INNER JOIN Magnet.dbo.NamesRecord NamesRecord
  ON NamesRecord.NameId = ContractRecord.DonorId
WHERE NameID > -1
AND (EXISTS (
  SELECT 1
  FROM Magnet.dbo.AddressRecord AddressRecord
  WHERE AddressRecord.AddressId = ContractRecord.FulfillmentAddressId
  AND BuildingFloor LIKE 'M%')
 OR EXISTS (
  SELECT 1
  FROM Magnet.dbo.AddressRecord AddressRecord
  WHERE AddressRecord.AddressId = ContractRecord.BillingAddressId
  AND BuildingFloor LIKE 'M%'))


SELECT SELECT NamesRecord.NameID, NamesRecord.FulfillmentAddressID NameFulfillmentAddressID, ContractRecord.FulfillmentAddressID, ContractRecord.BillingAddressId
FROM Magnet.dbo.ContractRecord ContractRecord
 INNER JOIN Magnet.dbo.NamesRecord NamesRecord
  ON NamesRecord.NameId = ContractRecord.DonorId
WHERE NameID > -1
AND (EXISTS (SELECT 1
  FROM Magnet.dbo.AddressRecord AddressRecord
  WHERE AddressRecord.AddressId IN (ContractRecord.FulfillmentAddressId, ContractRecord.BillingAddressId)
  AND BuildingFloor LIKE 'M%'))

The first query runs more than 10 times faster than the second. According to the Execution Plan, the first query uses two Clustered Index Scans with "BuildingFloor LIKE 'M%'" as the predicate, and an Index Seek on the ContractRecord for each of the sub-selects in the WHERE clause (40% cost per sub-select).

The second query uses a Clustered Index Seek with "BuildingFloor LIKE 'M%'" as the predicate, and a seek predicate for the AddressId constraints (96% cost). It's estimated row count is completely out as well (250 actual vs 1 estimated).

How can I improve the performance of the second query? Can I force SQL Server to choose an alternative strategy or do I have to modify the indexes on the tables?

Answer 1

Per-row subqueries are slow, as are disjunctive (or) filter conditions. Get rid of the subqueries entirely, and if you are using an or predicate in a filter you might think about replacing it with a union. Internally, the in gets translated into an or.

select
    NamesRecord.NameId
from (
    select
        ContractRecord.DonorId,
        ContractRecord.FulfillmentAddressId as AddressId
    from Magnet.dbo.ContractRecord ContractRecord
    union
    select
        ContractRecord.DonorId,
        ContractRecord.BillingAddressId as AddressId
    from Magnet.dbo.ContractRecord ContractRecord
) ContractRecordInfo
join Magnet.dbo.NamesRecord NamesRecord on 1=1
    and NamesRecord.NameId = ContractRecordInfo.DonorId
    and NamesRecord.NameId > -1
join Magnet.dbo.AddressRecord AddressRecord on 1=1
    and AddressRecord.AddressId = ContractRecordInfo.AddressId
    and AddressRecord.BuildingFloor like 'M%'