performance - SQL Server 较差的聚集索引查找性能?

  显示原文与译文双语对照的内容
97 5

我有两个查询:

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

第一个查询运行的速度比第二个查询快 10倍上。在执行计划中,第一个查询使用两个聚集索引扫描作为谓词,在ContractRecord子句中为每个子查询选择索引。

第二个查询使用聚集索引搜索"buildingfloor像'm%'"作为谓词,并且用于AddressId约束( 96%成本)的查询谓词。估计的行计数也完全超出了( 250实际 vs 1估计) 。

如何提高第二个查询的性能?我可以强制 SQL Server 选择替代策略还是我必须修改表的索引?

时间:原作者:0个回答

50 1

每行子查询都很慢,分离( or ) 过滤条件。完全消除子查询,如果在筛选器中使用 or 谓词,可以能会考虑用 union 替换它。在内部,in 被转换为 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%'
原作者:
...