本文介绍了在SQL Server中长时间运行连接需要花费很多时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好,



以下查询花了很多时间执行,即最多差不多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中长时间运行连接需要花费很多时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-28 03:21
查看更多