Question

我有一个简单的查询, 当我在条款中添加一条新行时, 多CH 运行速度会慢一点。我使用 SQL 服务器 16.0. 4120.1 (ExPRESS)

查询 1( 运行15 秒或以上) :

SELECT HSP.ServiceProviderID AS SP, *
FROM ALPCensus
LEFT JOIN ALPPriorApproval PA ON PA.SSNumber = ALPCensus.SSNumber  
LEFT JOIN ALPHeader ON ALPHeader.PartnerID = PA.PartnerID
LEFT JOIN HomeServiceProviders HSP ON HSP.ServiceProviderID = ALPHeader.ServiceProviderID
LEFT JOIN HomeMastResident HMR ON HMR.SSNumber = ALPCensus.SSNumber  
WHERE [Day] BETWEEN PA.StartDate AND PA.EndDate
AND [Day] <  2024-05-21 
AND PA.IsPending = 0
AND HSP.IsCapitation = 0
AND [Status] IN ( * , P )
AND Billed = 0
AND HSP.ServiceProviderID =  136   -- This is the line missing from Query #2

查询 2 (与上一个查询相同,但没有最后一行。运行需要小于一秒) :

SELECT HSP.ServiceProviderID AS SP, *
FROM ALPCensus
LEFT JOIN ALPPriorApproval PA ON PA.SSNumber = ALPCensus.SSNumber  
LEFT JOIN ALPHeader ON ALPHeader.PartnerID = PA.PartnerID
LEFT JOIN HomeServiceProviders HSP ON HSP.ServiceProviderID = ALPHeader.ServiceProviderID
LEFT JOIN HomeMastResident HMR ON HMR.SSNumber = ALPCensus.SSNumber  
WHERE [Day] BETWEEN PA.StartDate AND PA.EndDate
AND [Day] <  2024-05-21 
AND PA.IsPending = 0
AND HSP.IsCapitation = 0
AND [Status] IN ( * , P )
AND Billed = 0

以下是对问题1的执行计划:

< a href=>""https://i.static.net/rKyZKkkZ.png" rel="不跟随 nofollow noreferrer" > 进入此图像描述

以下是第2个问题的计划:

< a href=>""https://i.sstatic.net/JfjRyOw2.png" rel="不跟随 nofollow noreferrer" > 输入此图像描述

这些是我的数据库设置(如果有区别的话):

< a href=>""https://i.static.net/mLwFTGGD.png" rel="没有跟随 nofollow noreferrer" > 进入此图像描述

如果我将线条设置为 < code> 和 HSP. Service providerID is not NULL , 它也会运行得很快。我检查了确保列 < code> HSP. Service providerID 是一型而非某种无趣的数据类型。我运行的服务器似乎有大量的 CPU 和 RAM 资源。我相信我有我所需要的全部索引。

Answer 1

如果您只是在寻找 Captition = 0 和 ServivividerId = 136 是否只要加入 ALP 头板的表格, 并符合源表格中设定的条件?

SELECT HSP.ServiceProviderID AS SP, *
FROM ALPCensus
LEFT JOIN ALPPriorApproval PA ON PA.SSNumber = ALPCensus.SSNumber  
LEFT JOIN ALPHeader ON ALPHeader.PartnerID = PA.PartnerID
INNER JOIN
    ( 
    SELECT ServiceProviderID
     FROM HomeServiceProviders
     WHERE IsCapitation = 0 AND ServiceProviderID = 136
     ) AS HSP ON HSP.ServiceProviderID = ALPHeader.ServiceProviderID
LEFT JOIN HomeMastResident HMR ON HMR.SSNumber = ALPCensus.SSNumber  
WHERE [Day] BETWEEN PA.StartDate AND PA.EndDate
AND [Day] <  2024-05-21 
AND PA.IsPending = 0
AND [Status] IN ( * , P )
AND Billed = 0

友情链接