带有相关子查询的While循环的SQL Server性能调优

Question

在我的项目中，我遇到了以下T-SQL代码的挑战。

1. step1用父模块及其订阅的用户填充UserModules表
2. step2在Modules_Hierarchy表中检查与step1中的模块相关联的子模块，并通过将子模块与订阅用户的父模块映射到UserModules表中来插入有效的记录。此步骤将递归重复，直到找到所有子模块。

问题:

在step2中，WHILE循环和SELECT语句使用相关的子查询，并且表UserModules是INSERT和关联SELECT子句的一部分，这阻碍了性能，并且经常由于低于锁升级问题而导致查询失败。

ModulesUsers表的最终数据大小是4200万，并且预计还会增长。

Error Message: " SQL Server数据库引擎实例当前无法获取LOCK资源。当活跃用户较少时，重新运行语句。请数据库管理员检查此实例的锁和内存配置，或者检查长时间运行的事务。

如何优化这个查询即step2来解决这个问题?

步骤1:

INSERT INTO UserModules(ModuleID, UserID)
  SELECT ModuleID, UserID
  FROM TABLEA a
  INNER JOIN TABLEB b ON a.ID = b.ID

步骤2:

DECLARE @cnt int
SET @cnt = 1
WHILE( @cnt > 0 )      
BEGIN      
  SET @cnt = (SELECT COUNT(DISTINCT s.moduleid)
              FROM Modules_Hirarchy s WITH (nolock), Modules t      
              WHERE s.ParentModuleId = t.ModuleId      
              ------------      
                AND NOT EXISTS       
                 (SELECT ModuleId + EndUserId 
                  FROM UserModules  r      
                  WHERE s.moduleid = r.moduleid 
                    AND t.EndUserId = r.EndUserId)
                AND s.moduleid + t.EndUserId NOT IN 
                  (SELECT CAST(ModuleId AS varchar) + EndUserId 
                   FROM UserModules ))      
  IF @cnt = 0      
    BREAK      
  INSERT INTO UserModules (ModuleId, EndUserId)      
    SELECT DISTINCT s.moduleid, t.EndUserId       
    FROM Modules_Hirarchy s WITH (nolock), UserModules  t      
    WHERE s.ParentModuleId = t.ModuleId      
      AND NOT EXISTS       
       (SELECT ModuleId + EndUserId 
        FROM UserModules  r      
        WHERE s.moduleid = r.moduleid 
          AND t.EndUserId = r.EndUserId)
END  

Answer 1

一些数据来玩

create table #UserModules(ModuleID int, UserID int)
create table #Modules_Hirarchy(ParentModuleID int, ChildModuleID int)
insert into #UserModules (ModuleID , UserID)
values(1,1)
,(2,1)
,(3,1)
,(4,1)
,(5,1)
,(6,2)
,(7,2)
insert into #Modules_Hirarchy(ParentModuleID , ChildModuleID )
values (null,1)
,(1,2)
,(2,3)
,(3,4)
,(3,5)
,(null,6)
,(6,7)

的决议

with cts(ModuleID, UserID,parentModule ) as 
(
select a.ModuleID, a.UserID , CAST(null as int)as parentModule --, cAST(null as int)as b
from #UserModules a join #Modules_Hirarchy  b on a.ModuleID = b.ChildModuleID 
where b.ParentModuleID is null
union all
select b.ChildModuleID as ModuleID, a.UserID, b.ParentModuleID
from cts a join #Modules_Hirarchy b 
on a.ModuleID = b.ParentModuleID
)
select *
into #RESULT
from cts

编辑这很难说:)因为有很多变数但是你应该做些什么来提高查询的效率

1. 在列ModuleID ParentModuleID ChildModuleID上单独的非聚类索引

2. 你可能不想查询所有的组，而只想查询a显式的在锚中过滤掉尽可能多的组声明

   选择a.ModuleID, a.UserID, CAST(null as int)作为parentModulefrom #UserModules a join #Modules_Hirarchy b on a.ModuleID = b.c oldmoduleid其中b.ParentModuleID为空，a.ModuleId在(listOfModules)

3. 为列(ParentModuleID, ChildModuleID)添加唯一索引，因为非唯一行可能导致大量的行重复

除了它依赖于ParentModuleID ChildModuleID的数据选择性，但你不能做太多关于它

我认为它将工作良好的大数据集，因为谓词是简单的，只要数据选择性高