Poor Clustered Index Seek Performance?

I've got these two queries: SELECT SELECT NamesRecord.NameID, NamesRecord.FulfillmentAddressID NameFulfillmentAddressID, ContractRecord.FulfillmentAddressID, ContractRecord.Billing

Solution 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%'