Poor grouped performance indexing?

Question

I have 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 request is more than 10 times faster than the second. According to the Execution Plan, the first query uses two clustered indexers with “BuildingFloor LIKE“ M% ”as a predicate and an index search in ContractRecord for each of the subselects in the WHERE clause (40% south of the selection).

The second query uses the Clustered Index Seek with “BuildingFloor LIKE“ M% ”as the predicate and search predicate for the AddressId constraints (96% of the cost). He estimated the number of rows as well completely (250 valid versus 1 valid).

How to improve the performance of the second request? Can I get SQL Server to choose an alternative strategy or do I need to change indexes in tables?

Answer 1

String subqueries are tightened like conditions for a disjunctive ( or ) filter. Get rid of the entire subqueries, and if you use the or predicate in a filter, you might consider replacing it with union . Inside, in converted to 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%'