gpt4 book ai didi

sql-server - 聚集索引寻求性能不佳?

转载 作者:行者123 更新时间:2023-12-03 02:14:53 25 4
gpt4 key购买 nike

我有两个疑问:

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 倍以上。根据执行计划,第一个查询使用两次聚集索引扫描,以“BuildingFloor LIKE 'M%'”为谓词,并针对 WHERE 子句中的每个子选择在 ContractRecord 上进行索引查找(每个查询的成本为 40%)。子选择)。

第二个查询使用以“BuildingFloor LIKE 'M%'”为谓词的聚集索引查找,以及针对 AddressId 约束的查找谓词(96% 成本)。估计的行数也完全超出了(实际 250 行 vs 估计 1 行)。

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

最佳答案

每行子查询很慢,析取()过滤条件也很慢。完全摆脱子查询,如果您在过滤器中使用 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%'

关于sql-server - 聚集索引寻求性能不佳?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1340011/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com