Performance problem with query,

Other SQL Server Topics
1

DECLARE @To VARCHAR(20) = 'GM'
DECLARE @A TABLE(ID INT, Family VARCHAR(50), [Date] DATETIME, [Status] VARCHAR(50))
INSERT INTO @A
SELECT ID, Family, Create_at, CASE WHEN [Status] = 'New' THEN CASE WHEN DATEDIFF(HOUR, Create_at, GETDATE()) <= 24 THEN 'New' ELSE 'Past Due' END
WHEN [Status] = 'Customer-Accepted' THEN 'Pending Completion' END
FROM udt_Issue8DDataCar WITH (NOLOCK)
WHERE [Status] NOT LIKE 'Close%' AND [Status] != 'OnProgress' AND [Status] != 'AwaitingApproval' AND [Status] != 'Approved' AND [Status] != 'Rejected' AND [Status] != 'Customer-Rejected'

	DECLARE @AX TABLE(ID INT, Family VARCHAR(50), [Date] DATETIME, [Status] VARCHAR(50))
	INSERT INTO @AX
	SELECT ID, Family, Create_at, [Status] FROM udt_Issue8DDataCar WITH (NOLOCK)
	WHERE [Status] = 'OnProgress' OR [Status] = 'AwaitingApproval' OR [Status] = 'Approved' OR [Status] = 'Rejected' OR [Status] = 'Customer-Rejected'


	INSERT INTO @A
	SELECT A.ID, A.Family, A.[Date],
		CASE WHEN A.[Status] = 'OnProgress' THEN CASE WHEN B.EID IS NULL THEN CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 24 THEN 'On Progress' ELSE 'Past Due' END ELSE CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 168 THEN 'On Progress' ELSE 'Past Due' END END
			WHEN A.[Status] = 'AwaitingApproval' THEN CASE WHEN B.EID IS NULL THEN CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 24 THEN 'Awaiting Approval' ELSE 'Past Due' END ELSE CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 168 THEN 'Awaiting Approval' ELSE 'Past Due' END END
			WHEN A.[Status] = 'Approved' THEN CASE WHEN B.EID IS NULL THEN CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 24 THEN 'Customer''s Review' ELSE 'Past Due' END ELSE CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 168 THEN 'Customer''s Review' ELSE 'Past Due' END END
			WHEN A.[Status] = 'Rejected' THEN  CASE WHEN B.EID IS NULL THEN CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 24 THEN 'On Progress' ELSE 'Past Due' END ELSE CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 168 THEN 'On Progress' ELSE 'Past Due' END END
			WHEN A.[Status] = 'Customer-Rejected' THEN  CASE WHEN B.EID IS NULL THEN CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 24 THEN 'Rejected' ELSE 'Past Due' END ELSE CASE WHEN DATEDIFF(HOUR, A.[Date], GETDATE()) <= 168 THEN 'Rejected' ELSE 'Past Due' END END
		END AS [Status]
	FROM @AX A LEFT OUTER JOIN udt_8DHistory B WITH (NOLOCK) ON A.ID = B.EID AND B.[Action] = 'Rejected' AND [Who] = 'Customer'

DECLARE @PD TABLE(EID INT, TotalReject INT)
	INSERT INTO @PD
	SELECT EID, COUNT(*) AS TotalReject FROM udt_8DHistory WITH (NOLOCK)
	WHERE [Action] = 'Rejected' AND Who = 'Manager' AND [User] != 'btmjunej'
	GROUP BY EID

	DECLARE @PD_2 TABLE(EID INT, [Comment] VARCHAR(MAX))
	INSERT INTO @PD_2
	SELECT A.EID, A.Comment FROM udt_8DHistory A  WITH (NOLOCK) INNER JOIN (
	SELECT EID, MAX(ID) AS ID FROM udt_8DHistory  WITH (NOLOCK) WHERE [Action] = 'Rejected' AND [Who] = 'Manager' AND [User] != 'btmjunej'
	GROUP BY EID) B ON A.ID = B.ID

	DECLARE @PD_5 TABLE(ID INT, EID INT, [CFT Lead] VARCHAR(100), [RMA Source] VARCHAR(50), [Defect Code] VARCHAR(MAX), DefectLocation VARCHAR(500), Family VARCHAR(50), Origin VARCHAR(50), CreationDate DATETIME, Aging VARCHAR(50), [Status] VARCHAR(50), [To] VARCHAR(20))
	INSERT INTO @PD_5
	SELECT ROW_NUMBER() OVER (ORDER BY A.ID ASC) AS [ID], B.ID AS EID, C.Username AS 'CFT Lead', ReturnSite AS [RMA Source], CommentCustomer AS 'Defect Code', B.Location, A.Family, 
	OriginofNonconformity AS 'Origin', Create_at AS 'Creation Date', CASE WHEN DATEDIFF(HOUR, Create_at, GETDATE()) <= 24 THEN 
	CAST(DATEDIFF(HOUR, Create_at, GETDATE()) AS VARCHAR) + ' Hour(s)' ELSE CAST([dbo].CalcAgingWorkingDaysExcludeWeekendnHolidays(Create_at, GETDATE()) AS VARCHAR) + ' Day(s)'  END AS Aging, 
	CASE WHEN B.Status = 'OnProgress' THEN 'On Progress' WHEN B.[Status] = 'AwaitingApproval' THEN 'Awaiting <b>' + CASE WHEN ISNULL(ApprovalCounter, 0) = 0 THEN LEFT(C.AdditionalApprovalUsername, CHARINDEX(' ', C.AdditionalApprovalUsername) - 1) WHEN ISNULL(ApprovalCounter, 0) = 1 THEN LEFT(C.FirstLevelApprovalUsername, CHARINDEX(' ', + C.FirstLevelApprovalUsername) - 1) END + '</b> Approval'
	WHEN B.[Status] = 'Approved' THEN 'Customer''s Review' WHEN B.[Status] = 'Rejected' THEN 'Rejected By Manager' 
	WHEN B.[Status] = 'Customer-Rejected' THEN 'Rejected By Customer' ELSE B.[Status] END AS [Status], CASE
      WHEN dbo.[CALCAGINGWORKINGDAYS]('HOUR', B.Create_at, GETDATE()) >= 3 AND
        dbo.[CALCAGINGWORKINGDAYS]('HOUR', B.Create_at, GETDATE()) < 6 THEN 'Manager'
      WHEN dbo.[CALCAGINGWORKINGDAYS]('HOUR', B.Create_at, GETDATE()) >= 6 AND
        dbo.[CALCAGINGWORKINGDAYS]('HOUR', B.Create_at, GETDATE())%8 < 1 THEN 'Director'
      ELSE 'GM'
END AS [To]
	FROM @A A INNER JOIN udt_Issue8DDataCar B  WITH (NOLOCK) ON A.ID = B.ID
	INNER JOIN udt_8DEscalationMatrix C WITH (NOLOCK) ON A.Family = C.Family AND C.Department = 'PA'
	WHERE A.Status = 'Past Due'
select * from @PD_5
DECLARE @B TABLE(ID INT, [CFT Lead] VARCHAR(100), [RMA] VARCHAR(50), Defect VARCHAR(MAX), DefectLocation VARCHAR(1000), Family VARCHAR(50), Aging VARCHAR(50), [Status] VARCHAR(100), Reject INT, [To] VARCHAR(20))
INSERT INTO @B
	SELECT A.ID, A.[CFT Lead], A.[RMA Source], A.[Defect Code], A.DefectLocation, A.Family,-- A.Origin, A.CreationDate, 
	A.Aging, A.[Status], 
	ISNULL(B.TotalReject, 0) AS 'Reject', [To]
	FROM @PD_5 A LEFT OUTER JOIN @PD B ON A.EID = B.EID 
	LEFT OUTER JOIN @PD_2 C ON A.EID = C.EID
	WHERE A.EID NOT IN (SELECT EID FROM udt_8DInvalid)

IF EXISTS(SELECT 1 FROM @B WHERE [To] = @To)
BEGIN
DECLARE @Query VARCHAR(MAX)
SET @Query = '<html style="width:100%;font-family:arial, " helvetica neue ", helvetica, sans-serif;-webkit-text-size-adjust:100%;-ms-text-size-adjust:100%;padding:0;Margin:0;">New email @media only screen and (max-width:100%){p, ul li, ol li,a{font-size:16px!important}h1{font-size:30px!important;text-align:center}h2{font-size:26px!important;text-align:center}h3{font-size:20px!important;text-align:center}h1 a{font-size:30px!important}h2 a{font-size:26px!important}h3 a{font-size:20px!important}.es-menu td a{font-size:16px!important}.es-header-body p, .es-header-body ul li, .es-header-body ol li, .es-header-body a{font-size:16px!important}.es-footer-body p, .es-footer-body ul li, .es-footer-body ol li, .es-footer-body a{font-size:16px!important}.es-infoblock p, .es-infoblock ul li, .es-infoblock ol li, .es-infoblock a{font-size:12px!important}*[class="gmail-fix"]{display:none!important}.es-m-txt-c, .es-m-txt-c h1, .es-m-txt-c h2, .es-m-txt-c h3{text-align:center!important}.es-m-txt-r, .es-m-txt-r h1, .es-m-txt-r h2, .es-m-txt-r h3{text-align:right!important}.es-m-txt-l, .es-m-txt-l h1, .es-m-txt-l h2, .es-m-txt-l h3{text-align:left!important}.es-m-txt-r img, .es-m-txt-c img, .es-m-txt-l img{display:inline!important}.es-button-border{display:block!important}.es-button{font-size:20px!important;display:block!important;border-width:10px 0px 10px 0px!important}.es-btn-fw{border-width:10px 0px!important;text-align:center!important}.es-adaptive table,.es-btn-fw,.es-btn-fw-brdr,.es-left,.es-right{width:100%!important}.es-content table, .es-header table, .es-footer table,.es-content,.es-footer,.es-header{width:100%!important;max-width:100%!important}.es-adapt-td{display:block!important;width:100%!important}.adapt-img{width:100%!important;height:auto!important}.es-m-p0{padding:0px!important}.es-m-p0r{padding-right:0px!important}.es-m-p0l{padding-left:0px!important}.es-m-p0t{padding-top:0px!important}.es-m-p0b{padding-bottom:0!important}.es-m-p20b{padding-bottom:20px!important}.es-mobile-hidden,.es-hidden{display:none!important}.es-desk-hidden{display:table-row!important;width:auto!important;overflow:visible!important;float:none!important;max-height:inherit!important;line-height:inherit!important}.es-desk-menu-hidden{display:table-cell!important}table.es-table-not-adapt, .esd-block-html table{width:auto!important}table.es-social{display:inline-block!important}table.es-social td{display:inline-block!important}}#outlook a{padding:0}.ExternalClass{width:100%}.ExternalClass, .ExternalClass p, .ExternalClass span, .ExternalClass font, .ExternalClass td, .ExternalClass div{line-height:100%}.es-button{mso-style-priority:100!important;text-decoration:none!important}a[x-apple-data-detectors]{color:inherit!important;text-decoration:none!important;font-size:inherit!important;font-family:inherit!important;font-weight:inherit!important;line-height:inherit!important}.es-desk-hidden{display:none;float:left;overflow:hidden;width:0;max-height:0;line-height:0;mso-hide:all}<body style="width:100%;font-family:arial, " helvetica neue ", helvetica, sans-serif;-webkit-text-size-adjust:100%;-ms-text-size-adjust:100%;padding:0;Margin:0;">

<h1 style="Margin:0;-webkit-text-size-adjust:none;-ms-text-size-adjust:none;mso-line-height-rule:exactly;color:white;font-size:24px;font-family:arial, " helvetica neue ", helvetica, sans-serif;line-height:150%;color:#FFFFFF;">All Overdue CARs
<h3 style="Margin:0;line-height:120%;mso-line-height-rule:exactly;font-family:arial, " helvetica neue ", helvetica, sans-serif;font-size:20px;font-style:normal;font-weight:normal;color:#333333;">Dear Quality HOD
<p style="Margin:0;-webkit-text-size-adjust:none;-ms-text-size-adjust:none;mso-line-height-rule:exactly;font-size:14px;font-family:arial, " helvetica neue ", helvetica, sans-serif;line-height:150%;color:#333333;">Below is the list of all overdue CAR.
'

SET @Query += '

'

DECLARE Cur CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS ID, [CFT Lead], [RMA], Defect, DefectLocation, Family, Aging, Status, Reject FROM @B WHERE [To] = @To;
DECLARE @ID VARCHAR(10), @CFT VARCHAR(100), @RMA VARCHAR(50), @Defect VARCHAR(MAX), @Location VARCHAR(1000), @Family VARCHAR(50), @Aging VARCHAR(50), @Status VARCHAR(50), @Reject VARCHAR(50);
OPEN Cur;
FETCH NEXT FROM Cur INTO @ID, @CFT, @RMA, @Defect, @Location, @Family, @Aging, @Status, @Reject
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query += '











'
FETCH NEXT FROM Cur INTO @ID, @CFT, @RMA, @Defect, @Location, @Family, @Aging, @Status, @Reject;
END
CLOSE Cur
DEALLOCATE Cur
SET @Query += '
ID CFT Lead RMA Defect Location Family Aging Current Status Reject
' + @ID + ' ' + @CFT + ' ' + @RMA + ' ' + @Defect + ' ' + @Location + ' ' + @Family + ' ' + @Aging + ' ' + REPLACE(@Status, '''', '') + ' ' + @Reject + '
'

SET @Query += '

<h2 style="Margin:0;line-height:120%;mso-line-height-rule:exactly;font-family:arial, " helvetica neue ", helvetica, sans-serif;font-size:12px;font-style:normal;font-weight:normal;color:#FFFFFF;">This is an automated message. Please do not directly reply to this email account.
<p style="Margin:0;-webkit-text-size-adjust:none;-ms-text-size-adjust:none;mso-line-height-rule:exactly;font-size:14px;font-family:arial, " helvetica neue ", helvetica, sans-serif;line-height:150%;color:#FFFFFF;">
'

DECLARE @Query2 VARCHAR(MAX), @Email VARCHAR(100)
IF (@To = 'Manager')
BEGIN
SELECT TOP 1 @Email=AdditionalApprovalEmail + ';' + FirstLevelApprovalEmail FROM udt_8DEscalationMatrix
END
ELSE IF (@To = 'Director')
BEGIN
SELECT TOP 1 @Email=SecondLevelApprovalEmail FROM udt_8DEscalationMatrix
END
ELSE IF (@To = 'GM')
BEGIN
SELECT TOP 1 @Email=ThirdLevelApprovalEmail FROM udt_8DEscalationMatrix
END
SELECT @Email

END

2

2 things I would recommend.

  1. Don't use any table variables declare @AX but rather use #AX temp table and add an index on the column(s) that you will be using for joining and/or where clause
  2. Don't use loops with WHILE @@FETCH_STATUS = 0 but rather do it in one swoop if you can.
3

hello yosiasz,
i have updated, but it's still the same

4

Show us what you have