问题描述
大家好,
以下查询花了很多时间执行,即最多差不多2小时,有时甚至更多。表LAR_PRODUCTS和STORE_RANGE_GRP_MATCH是物理表,分别包含432837和103038的记录计数。欢迎任何减少查询执行时间的建议。
Hi All,
The below query is taking a lot of time in executing i.e. upto almost 2 hr and sometime more also. The Tables LAR_PRODUCTS and STORE_RANGE_GRP_MATCH are physical tables and contain a record count of 432837 and 103038 respectively. Any suggestion to reduce the query execution time is welcomed.
Select 1
From LAR_PRODUCTS prd with (nolock)
Join STORE_RANGE_GRP_MATCH srg with (nolock)
On prd.Store_Range_Grp_Id = srg.Orig_Store_Range_Grp_ID
And srg.Match_Flag = 'Y'
And prd.Range_Event_Id = srg.LAR_Range_Event_Id
Where srg.Range_Event_Id Not IN (Select Range_Event_Id
From Last_Authorised_Range
我尝试过:
我曾尝试在两个表上使用索引但仍然需要花费大量时间
What I have tried:
I have tried using indexed on both the tables but still it's taking a lot of time
推荐答案
LEFT join Last_Authorised_Range lar on lar.Range_Event_Id = srg.Range_Event_Id
WHERE lar.Range_Event_Id is null
ID字段中的索引表格也应该有所帮助。
正如会员10454138指出的那样,在 Match_Flag 如果只有两个值
关于我在列上的最后一点只有两个值 - @RichardDeeming在一个很好的点上以上评论。如果您总是要搜索 Match_Flag ='Y'
,那么使用过滤索引将带来性能提升 - 参考 []
Indexes on the ID fields in the tables should also help.
As Member 10454138 points out, there is no major benefit in having an index on Match_Flag
if there are only two values
In regard to my last point on the column that only has two values - @RichardDeeming made an excellent point in a comment above. If you are always going to search for Match_Flag='Y'
then using a Filtered Index will bring performance improvement - reference Introduction to SQL Server Filtered Indexes[^]
这篇关于在SQL Server中长时间运行连接需要花费很多时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!