I am trying to analyse execution time of a query. I used the following statement to check CPU and elapsed time:
SET STATISTICS IO, TIME ON;
For a single query, I can understand the CPU and elapsed time. But when it comes to a complex query, say as follows (Pretty big and for the time being, not sharing any sample data):
DECLARE @userID BIGINT = 100, @type bit =0
DECLARE @fetchDate DATETIME;
SET @fetchDate = DATEADD(yy,-2,datediff(d,0,getdate()))
DECLARE @subuserId BIGINT;
SELECT @subuserId = dbo.fn_getSubstituteForUser(@userID)
DECLARE @userGradeLevelCode NVARCHAR(20);
SELECT @userGradeLevelCode = GradeLevelCode
FROM Tbl_UserPost pst
LEFT JOIN Tbl_MasterGradeLevel gd ON pst.GradeLevel_ID = gd.GradeLevel_ID
WHERE pst.IsActive = 1 AND pst.User_ID = @userID
SELECT
RANK() OVER (PARTITION BY MemoForAllID ORDER BY MemoForAllDetailID DESC) r,
SenderID, MemoForAllID,
ReceiverIndividualIDs, MemoForAllDetailID,
Status, IsUpdated, IsTransfered, CreatedBy
INTO
#temp_VW_Tbl_MemoForAllDetail
FROM
VW_Tbl_MemoForAllDetail
WHERE
IsActive = 1 AND CreatedOn > @fetchDate
SELECT
header.MemoForAllID,
detail.MemoForAllDetailID,
header.Subject,
header.Code,
header.DocumentNumber,
detail.SenderID,
CASE
WHEN (dd.GradeLevelCode = 'DEL')
THEN dd.DepartmentName
WHEN @type = 0 OR detail.Status IN ('REVIEW', 'DRAFT', 'CORRECTION')
THEN dd.EmployeeName
ELSE
CASE
WHEN (@userID IN (SELECT dlg1.DelegateID
FROM Tbl_MemoForAllDelegateDetail dlg1
WHERE dlg1.MemoForAllID = header.MemoForAllID
AND dlg1.IsActive = 1)
AND detail.Status NOT IN ('REVIEW', 'CORRECTION'))
THEN
(SELECT DirectorateName
FROM tbl_MasterDirectorate msd
INNER JOIN tbl_UserPost pst ON pst.Directorate_ID = msd.Directorate_ID
WHERE pst.IsActive = 1
AND pst.User_ID = (SELECT TOP 1 dlg1.SenderID
FROM Tbl_MemoForAllDelegateDetail dlg1
WHERE dlg1.MemoForAllID = header.MemoForAllID
AND dlg1.DelegateID = @userID
AND dlg1.IsActive = 1))
ELSE
(SELECT DirectorateName
FROM tbl_MasterDirectorate msd
INNER JOIN tbl_UserPost pst ON pst.Directorate_ID = msd.Directorate_ID
WHERE pst.IsActive = 1
AND pst.User_ID = header.SenderID)
END
END AS SenderName,
header.MemoForAllDate,
header.Priority AS PriorityName,
detail.Status,
CASE
WHEN (@userID IN (SELECT dlg1.DelegateID
FROM Tbl_MemoForAllDelegateDetail dlg1
WHERE dlg1.MemoForAllID = header.MemoForAllID
AND dlg1.IsActive = 1)
AND detail.Status NOT IN ('REVIEW', 'CORRECTION'))
THEN (SELECT TOP 1 dlg1.CreatedOn
FROM Tbl_MemoForAllDelegateDetail dlg1
WHERE dlg1.MemoForAllID = header.MemoForAllID
AND dlg1.DelegateID = @userID
AND dlg1.IsActive = 1)
ELSE header.MemoForAllDate
END AS SentOn,
CASE
WHEN (@userID IN (SELECT value FROM STRING_SPLIT(header.CCIDs, ',')))
THEN CAST(1 AS BIT)
WHEN (@userID IN (SELECT value FROM STRING_SPLIT(header.CCSubIDs, ',')))
THEN CAST(1 AS BIT)
WHEN (@userID IN (SELECT value FROM STRING_SPLIT(dlgt.CCDelegateIDs, ',')))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS IsCCUser,
CASE
WHEN (@subuserId <> 0)
THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT)
END AS CanTakeAction,
detail.IsUpdated,
CASE
WHEN (@userID IN (SELECT a.SenderID
FROM Tbl_MemoForAllDelegateDetail a
WHERE a.MemoForAllID = header.MemoForAllID
AND a.IsActive = 1))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS HasAssignedDelegate,
CASE
WHEN (@userID IN (SELECT value
FROM STRING_SPLIT(header.RecipientIDs, ','))
AND detail.Status NOT IN ('REVIEW', 'CORRECTION'))
THEN CAST(1 AS BIT)
WHEN (@userID IN (SELECT value FROM STRING_SPLIT(header.RecipientSubIDs, ','))
AND detail.Status NOT IN ('REVIEW', 'CORRECTION'))
THEN CAST(1 AS BIT)
WHEN (@userID IN (SELECT dlg.DelegateID FROM Tbl_MemoForAllDelegateDetail dlg WHERE dlg.MemoForAllID = header.MemoForAllID AND dlg.IsActive = 1 ) AND detail.Status NOT IN ('REVIEW', 'CORRECTION'))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS CanFreeze,
CASE WHEN (@userID IN (SELECT a.User_ID FROM Tbl_MemoForAllFreezeDetail a WHERE a.MemoForAllID = header.MemoForAllID AND a.IsActive = 1 ))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS HasFreezed,
CASE WHEN (
header.MemoForAllID IN (SELECT DISTINCT a.MemoForAllRefID FROM Tbl_MemoForAllHeader a WHERE a.CreatedBy = @userID and CreatedOn > @fetchDate)
AND @userID IN (SELECT a.User_ID FROM Tbl_MemoForAllFreezeDetail a WHERE a.MemoForAllID = header.MemoForAllID AND a.IsActive = 1 )
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS IsReffered,
'MemoForAll' MemoType,
CAST(0 AS BIT) IsMemoClosed,
CAST(0 AS BIT) IsFinalSent,
CASE WHEN (@userID IN (SELECT dlg.DelegateID FROM Tbl_MemoForAllDelegateDetail dlg WHERE dlg.MemoForAllID = header.MemoForAllID AND dlg.IsActive = 1 ) AND detail.Status NOT IN ('REVIEW', 'CORRECTION'))
THEN CAST(1 AS bigint)
ELSE CAST(0 AS bigint)
END AS RecipientDelegate_ID,
CAST(0 AS BIT) IsForwarded,
CASE WHEN (header.Status NOT IN ('DRAFT','REVIEW') AND @userGradeLevelCode IN ('DIR'))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS CanTransfer,
detail.IsTransfered,
detail.CreatedBy
FROM Tbl_MemoForAllHeader header
LEFT JOIN Tbl_MemoForAllDelegateDetail dlgt ON header.MemoForAllID = dlgt.MemoForAllID AND dlgt.ID = (
SELECT TOP 1 ID FROM Tbl_MemoForAllDelegateDetail
WHERE MemoForAllID = header.MemoForAllID AND IsActive = 1
)
INNER JOIN
#temp_VW_Tbl_MemoForAllDetail detail
ON (header.MemoForAllID = detail.MemoForAllID)
inner join VW_UserInfo dd on dd.User_ID=detail.SenderID
WHERE
detail.r =1 and header.CreatedOn > @fetchDate
and header.IsActive = 1 AND header.IsForAllDirectors = 0
AND (
(header.IsForAllDirectors = 0 AND header.Status IN ('ADD_DELEGATE') AND @userID IN(SELECT value FROM STRING_SPLIT(header.RecipientIDs,',')) )
OR @userID IN(SELECT value FROM STRING_SPLIT(header.CCSecretaryIDs,','))
OR (header.Status IN ('REVIEW') AND (header.OriginatorID = @userID OR header.OriginatorSubID = @userID))
)
AND header.Status NOT IN ('DRAFT')
DROP TABLE #temp_VW_Tbl_MemoForAllDetail
The performance degrades in production, in my understanding it could be due to no. of CAST() and subqueries (As I didn't write the actual query) with CASE statement. If any suggestion provided on assumption, would be glad to know more about optimizing.
Now my actual query is something different that I am unable to figure out. When I run the query on production, I get the following:
Image 1:
For 4970 rows, it takes around 00:01:20. In local, it's pretty fast. When I use this statement SET STATISTICS IO, TIME ON;, got the below:
Image 2:
My question is, the execution time I get in the first image would be the same with the second image. I am willing to know like how the calculation done in the second image for execution time of the query. I can see no. of elapsed time for each scan, to sum up and to get the actual execution time should I calculate all the elapsed time in the second image?
Any brief explanation would be great.
N.B: I am pretty novice in analysing SQL queries, please pardon if I missed anything or suggest anything that can improve the post.