如何优化存储过程dbo.sproc_GetCountByUserId 移除临时表实现相同功能

优化核心思路：将原存储过程中两次临时表的中转逻辑，直接合并为关联查询+单次聚合，完全消除临时表开销，同时所有统计判定规则和原逻辑完全一致。

优化后完整代码 #

CREATE OR ALTER PROCEDURE dbo.sproc_GetCountByUserId
(
   @UserID INT
)
AS
DECLARE @MaxDate SMALLDATETIME = DATEADD(hh, - 36, GETUTCDATE());

SELECT
    SUM(CASE WHEN (usr.UserID = gc.UserID AND usr.DateUTC <= @MaxDate) = 0 THEN 1 ELSE 0 END) New,
    SUM(CASE WHEN (usr.UserID = gc.UserID AND usr.DateUTC <= @MaxDate) = 0 
              AND (usr.lCustomerID = gc.CustomerID AND usr.CustomerTypeID = 4) = 0 THEN 1 ELSE 0 END) Expired,
    SUM(CASE WHEN (usr.lCustomerID = gc.CustomerID AND usr.CustomerTypeID = 4) = 1 THEN 1 ELSE 0 END) Valid
FROM
    dbo.UsersTable usr WITH (NOLOCK, NOEXPAND)
INNER JOIN 
    dbo.Customer gc WITH (NOLOCK) 
    ON gc.CustomerID = usr.CustomerID AND gc.UserID = @UserID
WHERE
    usr.UserID = @UserID
    AND usr.CustomerTypeID IN (1,2,4,5,6,7,8,9,10);

逻辑一致性说明 #

• 原临时表#users的过滤条件直接挪到最终查询的WHERE子句，过滤范围完全一致
• 原HasTask、HasReply的判定逻辑直接内嵌到聚合的CASE语句中，判定规则和原逻辑完全相同
• 关联条件和原逻辑完全一致，没有增减数据范围

内容的提问来源于stack exchange，提问作者Chatra