CODEWITHSUNDEEP
sqlstored-procedures
Chatra
Chatra

Reputation: 3139

Optimize stored procedure removing temp tables

I have the below stored procedure and I want to optimize it without using temp tables.

CREATE OR ALTER PROCEDURE dbo.sproc_GetCountByUserId
(
   @UserID INT
)
AS

DECLARE @MaxDate SMALLDATETIME = DATEADD(hh, - 36, GETUTCDATE());

SELECT
       usr.UserID,
       usr.CustomerID,
       usr.CustomerTypeID,
       usr.DateUTC
INTO
       #users
FROM
       dbo.UsersTable usr WITH (NOLOCK, NOEXPAND)
WHERE
       usr.UserID = @UserID
       AND usr.CustomerTypeID IN (1,2,4,5,6,7,8,9,10);

SELECT
       t.CustomerTypeID, 
       t.CustomerID,
       t.DateUTC,
       CASE WHEN t.UserID = gc.UserID AND t.DateUTC <= @MaxDate THEN 1 ELSE 0 END HasTask,
       CASE WHEN t.lCustomerID = gc.CustomerID AND t.CustomerTypeID = 4 THEN 1 ELSE 0 END HasReply
INTO
       #count
FROM
       #users t
       JOIN dbo.Customer gc WITH (NOLOCK) ON gc.CustomerID = t.CustomerID AND gc.UserID = @UserID

SELECT
       SUM(CASE WHEN t.HasTask = 0 THEN 1 ELSE 0 END) New,
       SUM(CASE WHEN t.HasTask = 0 AND t.HasReply = 0  THEN 1 ELSE 0 END) Expired,
       SUM(CASE WHEN t.HasReply = 1 THEN 1 ELSE 0 END) Valid
FROM
       #count t

Upvotes: 0

Views: 47

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

If you just want to rewrite your code, You can write it in below manner -

CREATE OR ALTER PROCEDURE dbo.sproc_GetCountByUserId
(
   @UserID INT
)
AS

DECLARE @MaxDate SMALLDATETIME = DATEADD(hh, - 36, GETUTCDATE());

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

Upvotes: 1

Related Questions