存储过程执行时间超过30秒。

Question

我正在优化存储过程，在这个过程中，我在一个表变量中获得超过10K用户ids的列表作为参数。如果表变量中有任何数据，我将设置标志@ContainsUserIds。在主查询中，选择表变量中存在的所有用户，如果表变量中没有数据，则选择所有用户( where子句中有更多条件)。

问题在于，where子句中的语句超过30秒，具有OR条件。如果我删除了检查@ContainsUserIds =0的第一个条件，那么它将在一秒钟内执行。有人能帮我优化这个查询吗？

此存储过程是从不同位置调用的，因此用户ids可能不会传递。

CREATE PROCEDURE [core].[spGetUsersByFilter]      
(      
  @ClientId                nvarchar(38)  = NULL,        
  @UserIds                 [UserIdList] readonly      
)      
AS      
BEGIN   
    SET NOCOUNT ON         
      
    DECLARE @ContainsUserIds BIT = 0,
    SET @ContainsUserIds = CASE 
                                WHEN EXISTS(SELECT TOP 1 1 FROM @UserIds) 
                                    THEN 1 
                                ELSE 0 
                            END;   

    SELECT DISTINCT ES.Id
    FROM [db].[Users] E
    JOIN [db].[UserDetails] ES ON ES.UserId = E.Id 
    WHERE E.[Active] = 1  
         AND (@ContainsUserIds = 0 OR E.UserId IN(SELECT Item FROM @UserIds)) 
         AND ES.ClientId = @ClientId
END 

Answer 1

众所周知，SQL不喜欢OR，优化器有时会围绕它工作，但在大多数情况下，将事情转换为UNION [ALL]语法是个好主意。如果只有1 OR，这通常是很容易做到的，如果有多个东西爆炸很快。

无论如何，您可以将存储过程转换为：

CREATE PROCEDURE [core].[spGetUsersByFilter]      
(      
  @ClientId                nvarchar(38)  = NULL,        
  @UserIds                 [UserIdList] readonly      
)      
AS      
BEGIN   
    SET NOCOUNT ON         
      
    DECLARE @ContainsUserIds BIT = 0,
    SET @ContainsUserIds = CASE 
                                WHEN EXISTS(SELECT * FROM @UserIds) 
                                    THEN 1 
                                ELSE 0 
                            END;   

    SELECT DISTINCT ES.Id
    FROM [db].[Users] E
    JOIN [db].[UserDetails] ES ON ES.UserId = E.Id 
    WHERE E.[Active] = 1  
         AND (@ContainsUserIds = 0) 
         AND ES.ClientId = @ClientId
         
         
    UNION ALL
    
    SELECT DISTINCT ES.Id
    FROM [db].[Users] E
    JOIN [db].[UserDetails] ES ON ES.UserId = E.Id 
    WHERE E.[Active] = 1  
         AND (@ContainsUserIds = 1)
         AND E.UserId IN (SELECT Item FROM @UserIds)) 
         AND ES.ClientId = @ClientId
END 

此外，如果表变量包含多个记录，您可能更喜欢使用临时表，因为后者允许索引，最重要的是使用统计数据处理，这将导致更好的执行计划。我也更喜欢JOIN而不是IN ()，只是确保在JOINing时没有加倍的值。

CREATE PROCEDURE [core].[spGetUsersByFilter]      
(      
  @ClientId                nvarchar(38)  = NULL,        
  @UserIds                 [UserIdList] readonly      
)      
AS      
BEGIN   
    SET NOCOUNT ON         
    
    SELECT DISTINCT Item 
      INTO #UserIds
      FROM @UserIds
      
    IF @@ROWCOUNT = 0
        BEGIN
            
            SELECT DISTINCT ES.Id
            FROM [db].[Users] E
            JOIN [db].[UserDetails] ES ON ES.UserId = E.Id 
            WHERE E.[Active] = 1                 
              AND ES.ClientId = @ClientId;
              
        END
    ELSE
        BEGIN
            CREATE UNIQUE INDEX uq0_UserIds ON #UserIds ( Item ) WITH (FILLFACTOR = 100);
            
            SELECT DISTINCT ES.Id
            FROM [db].[Users] E
            JOIN [db].[UserDetails] ES ON ES.UserId = E.Id 
            JOIN #UserIds T ON T.Item = E.UserId
            WHERE E.[Active] = 1  
                 AND ES.ClientId = @ClientId
        END
END 

PS:以上都是用记事本写的，未经测试，可能需要一些组装=)