October 9, 2017 at 10:42 am
Hi All,
I have written a stored procedure for one of my application functionalities where it goes and fetches the total no of active workitem records from the table.
The problem is I have around 88671 records in the workitem table, so the sp takes on an average of 15 to 21 seconds to fetch the records based on conditions to get completed.
It slows down the whole process. Please help me in improving the query.
Sp is below : ALTER PROCEDURE [dbo].[pGetWorkitems] @AccountNumber char(10) = NULL,
@WorkItemTypeId int = NULL,
@EvalYear varchar(40) = NULL,
@EvalMonth varchar(40) = NULL,
--@EvalDate datetime2(3)=NULL,
@AssignedTo varchar(20) = NULL,
@QVAssignedTo varchar(20) = NULL,
@WorkItemStatusId varchar(1000) = NULL,
@ActNum_Flag char(1) = NULL,
@WIType_Flag char(1) = NULL,
@EvalDate_Flag char(1) = NULL,
@CIMEffDate_Flag char(1) = NULL,
@Plan_Flag char(1) = NULL,
@Status_Flag char(1) = NULL,
@BillMethod_Flag char(1) = NULL,
@Priority_Flag char(1) = NULL,
@StartDate_Flag char(1) = NULL,
@ModifiedDate_Flag char(1) = NULL,
@AccountName_Flag char(1) = NULL,
@AssignedTo_Flag char(1) = NULL,
@QVAssignedTo_Flag char(1) = NULL,
@EndDate_Flag char(1) = NULL,
@Threshold int,
@PageNumberselected int
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @Query varchar(3000),
@WorkItemWhereClause varchar(2000),
@WhereClause varchar(2000),
@OrderClause varchar(2000),
@SortOrderClause varchar(3000),
@PaginationQuery varchar(3000),
@PageWhereClause varchar(2000),
@StartRecordNum int,
@EndRecordNum int
DECLARE @MailWhereClause varchar(2000),
@MailQuery varchar(3000),
@AdminWorkQuery varchar(3000)
SELECT
@StartRecordNum = (@Threshold * (@PageNumberselected - 1))
SELECT
@EndRecordNum = @Threshold * @PageNumberselected
SELECT
@PageWhereClause = ' '
PRINT @StartRecordNum
PRINT @EndRecordNum
CREATE TABLE #tempPopulateAdmin (
WorkItemId numeric(10, 0),
AccountNumber char(10),
AccountName varchar(50),
WorkItemTypeId numeric(10, 0),
WorkItemTypeDesc varchar(50),
AssignedTo varchar(120),
QVAssignedTo varchar(120),
PlanCode varchar(1),
PlanNumber int,
EvalDate datetime2(3),
CIMEffectiveDate datetime2(3),
WorkItemStatusId numeric(10, 0),
WorkItemStatusDesc varchar(50),
BillingMethodId numeric(10, 0),
BillingMethodDesc varchar(50),
Priority int,
StartTime datetime2(3),
ModifiedDate datetime2(3),
EndTime datetime2(3),
PriorityDesc varchar(50)
)
CREATE TABLE #tempMailWorkItem (
WorkItemId numeric(10, 0),
AccountNumber char(10),
AccountName varchar(50),
WorkItemTypeId numeric(10, 0),
WorkItemTypeDesc varchar(50),
AssignedTo varchar(20),
QVAssignedTo varchar(20),
PlanCode varchar(1),
PlanNumber int,
EvalDate datetime2(3),
CIMEffectiveDate datetime2(3),
WorkItemStatusId numeric(10, 0),
WorkItemStatusDesc varchar(50),
BillingMethodId numeric(10, 0),
BillingMethodDesc varchar(50),
Priority int,
--PriorityDesc varchar(50),
StartTime datetime2(3),
ModifiedDate datetime2(3),
EndTime datetime2(3)
--PriorityDesc varchar(50)
)
CREATE TABLE #tempAdminWorkQueue (
WorkItemId numeric(10, 0),
AccountNumber char(10),
AccountName varchar(50),
WorkItemTypeId numeric(10, 0),
WorkItemTypeDesc varchar(50),
AssignedTo varchar(20),
QVAssignedTo varchar(20),
PlanCode varchar(1),
PlanNumber int,
EvalDate datetime2(3),
CIMEffectiveDate datetime2(3),
WorkItemStatusId numeric(10, 0),
WorkItemStatusDesc varchar(50),
BillingMethodId numeric(10, 0),
BillingMethodDesc varchar(50),
Priority int,
StartTime datetime2(3),
ModifiedDate datetime2(3),
EndTime datetime2(3),
PriorityDesc varchar(50)
)
SELECT
@WorkItemWhereClause = 'WHERE
W.AccountNumber = A.AccountNumber and
W.WorkItemTypeId= WI.WorkItemTypeId and
--W.WorkItemTypeId not in (8) and
W.WorkItemStatusId = WS.WorkItemStatusId and
W.Priority >= WP.PriorityValueLow and
W.Priority <= WP.PriorityValueHigh and
(W.BillingMethodId IS NULL OR W.BillingMethodId = BI.BillingMethodId)'
SELECT
@MailWhereClause = 'WHERE
W.AccountNumber = A.AccountNumber and
W.WorkItemTypeId= WI.WorkItemTypeId and
W.Priority is null and
W.WorkItemStatusId = WS.WorkItemStatusId and
(W.BillingMethodId IS NULL OR W.BillingMethodId = BI.BillingMethodId)'
SELECT
@WhereClause = 'WHERE
W.AccountNumber = A.AccountNumber '
SELECT
@OrderClause = 'ORDER BY '
/* IF @EvalDate_Flag IS NULL OR @EvalDate_Flag = 'Y'
BEGIN
SELECT @OrderClause ='ORDER BY W.EvalDate DESC'
END
IF @EvalDate_Flag = 'N'
BEGIN
SELECT @OrderClause ='ORDER BY W.EvalDate ASC'
END
IF @AccountNumber IS NOT NULL
BEGIN
SELECT
@WhereClause = @WhereClause + ' AND W.AccountNumber = ' + '''' + @AccountNumber + ''''
END
IF @WorkItemTypeId IS NOT NULL
BEGIN
SELECT
@WhereClause = @WhereClause + ' AND W.WorkItemTypeId = ' + CONVERT(char, @WorkItemTypeId)
END
IF @EvalDate IS NOT NULL
BEGIN
SELECT @WhereClause = @WhereClause + ' AND W.EvalDate = ' + ''''+ convert(char(15),@EvalDate,101)+''''
END
IF @EvalYear IS NOT NULL
BEGIN
SELECT
@WhereClause = @WhereClause + ' AND CONVERT(SMALLINT, DATEPART(YEAR, W.EvalDate))= ' + '''' + CONVERT(char(15), @EvalYear, 101) + ''''
END
IF @EvalMonth IS NOT NULL
BEGIN
SELECT
@WhereClause = @WhereClause + ' AND CONVERT(SMALLINT, DATEPART(MONTH, W.EvalDate)) = ' + '''' + CONVERT(char(15), @EvalMonth, 101) + ''''
END
IF @AssignedTo IS NOT NULL
BEGIN
SELECT
@WhereClause = @WhereClause + ' AND W.AssignedTo = ' + '''' + @AssignedTo + ''''
END
IF @QVAssignedTo IS NOT NULL
BEGIN
SELECT
@WhereClause = @WhereClause + ' AND W.QVAssignedTo = ' + '''' + @QVAssignedTo + ''''
END
IF @WorkItemStatusId IS NOT NULL
BEGIN
SELECT
@WhereClause = @WhereClause + ' AND W.WorkItemStatusId IN ( ' + @WorkItemStatusId + ')'
END
IF
@ActNum_Flag IS NULL
AND
@WIType_Flag IS NULL
AND @EvalDate_Flag IS NULL
AND @CIMEffDate_Flag IS NULL
AND @Plan_Flag IS NULL
AND @Status_Flag IS NULL
AND @BillMethod_Flag IS NULL
AND @Priority_Flag IS NULL
AND @StartDate_Flag IS NULL
AND @ModifiedDate_Flag IS NULL
AND @AccountName_Flag IS NULL
AND --ADDED FEW MORE COLUMNS FOR SORTING for UAT defect
@AssignedTo_Flag IS NULL
AND @QVAssignedTo_Flag IS NULL
AND @EndDate_Flag IS NULL
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.EvalDate DESC'
END
ELSE
BEGIN
IF @ActNum_Flag IS NOT NULL
BEGIN
IF @ActNum_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.AccountNumber desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.AccountNumber asc '
END
END
ELSE
IF @WIType_Flag IS NOT NULL
BEGIN
IF @WIType_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.WorkItemTypeId desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.WorkItemTypeId asc '
END
END
ELSE
IF @EvalDate_Flag IS NOT NULL
BEGIN
IF @EvalDate_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.EvalDate desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.EvalDate asc '
END
END
ELSE
IF @Plan_Flag IS NOT NULL
BEGIN
IF @Plan_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.PlanCode desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.PlanCode asc '
END
END
ELSE
IF @CIMEffDate_Flag IS NOT NULL
BEGIN
IF @CIMEffDate_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.CIMEffectiveDate desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.CIMEffectiveDate asc '
END
END
ELSE
IF @Status_Flag IS NOT NULL
BEGIN
IF @Status_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.WorkItemStatusId desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.WorkItemStatusId asc '
END
END
ELSE
IF @BillMethod_Flag IS NOT NULL
BEGIN
IF @BillMethod_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.BillingMethodId desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.BillingMethodId asc '
END
END
ELSE
IF @Priority_Flag IS NOT NULL
BEGIN
IF @Priority_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.Priority desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.Priority asc '
END
END
ELSE
IF @StartDate_Flag IS NOT NULL
BEGIN
IF @StartDate_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.StartTime desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.StartTime asc '
END
END
ELSE
IF @AccountName_Flag IS NOT NULL
BEGIN
IF @AccountName_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'A.AccountName desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'A.AccountName asc '
END
END
ELSE
IF @AssignedTo_Flag IS NOT NULL
BEGIN
IF @AssignedTo_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.AssignedTo desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.AssignedTo asc '
END
END
ELSE
IF @QVAssignedTo_Flag IS NOT NULL
BEGIN
IF @QVAssignedTo_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.QVAssignedTo desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.QVAssignedTo asc '
END
END
ELSE
IF @EndDate_Flag IS NOT NULL
BEGIN
IF @EndDate_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.EndTime desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.EndTime asc '
END
END
ELSE
IF @ModifiedDate_Flag IS NOT NULL
BEGIN
IF @ModifiedDate_Flag = 'Y'
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.ModifiedDate desc '
END
ELSE
BEGIN
SELECT
@OrderClause = @OrderClause + 'W.ModifiedDate asc '
END
END
END
SELECT
@Query =
'INSERT INTO #tempPopulateAdmin
SELECT
DISTINCT
W.WorkItemId,
W.AccountNumber,
A.AccountName,
W.WorkItemTypeId,WI.WorkItemTypeDesc ,
(select UserName as AssignedTo from WorkItemAssigneeUserNames WA where W.AssignedTo=WA.CID),
(select UserName as QVAssignedTo from WorkItemAssigneeUserNames WA where W.QVAssignedTo=WA.CID),W.PlanCode,W.PlanNumber,W.EvalDate,W.CIMEffectiveDate,W.WorkItemStatusId,WS.WorkItemStatusDesc,
W.BillingMethodId,
(CASE WHEN ( SELECT BI.BillingMethodDesc FROM BillingMethod BI
WHERE W.BillingMethodId = BI.BillingMethodId)
IS NOT NULL THEN BI.BillingMethodDesc ELSE NULL END) AS BillingMethodDesc,
W.Priority,
W.StartTime,W.ModifiedDate,W.EndTime,
WP.PriorityDesc
FROM WorkItem W
LEFT OUTER JOIN WorkItemAssigneeUserNames WA ON W.AssignedTo=WA.CID and W.QVAssignedTo=WA.CID
, Account A,WorkItemType WI,WorkItemStatus WS,BillingMethod BI,WorkItemPriority WP ' + @WorkItemWhereClause
PRINT @Query
EXECUTE (@Query)
PRINT 'DATA INSERTED INTO TEMP TABLE'
SELECT
@MailQuery =
'INSERT INTO #tempMailWorkItem
SELECT
DISTINCT
W.WorkItemId,
W.AccountNumber,
A.AccountName,
W.WorkItemTypeId,WI.WorkItemTypeDesc,(select UserName as AssignedTo from WorkItemAssigneeUserNames WA where W.AssignedTo=WA.CID),
(select UserName as QVAssignedTo from WorkItemAssigneeUserNames WA where W.QVAssignedTo=WA.CID) ,
W.PlanCode ,W.PlanNumber,W.EvalDate,W.CIMEffectiveDate,W.WorkItemStatusId,WS.WorkItemStatusDesc,
W.BillingMethodId,
(CASE WHEN ( SELECT BI.BillingMethodDesc FROM BillingMethod BI
WHERE W.BillingMethodId = BI.BillingMethodId)
IS NOT NULL THEN BI.BillingMethodDesc ELSE NULL END) AS BillingMethodDesc,
W.Priority,
--WP.PriorityDesc,
W.StartTime,W.ModifiedDate,W.EndTime
-- WP.PriorityDesc
FROM WorkItem W
LEFT OUTER JOIN WorkItemAssigneeUserNames WA ON W.AssignedTo=WA.CID and W.QVAssignedTo=WA.CID
, Account A,WorkItemType WI,WorkItemStatus WS,BillingMethod BI,WorkItemPriority WP ' + @MailWhereClause
PRINT @MailQuery
EXECUTE (@MailQuery)
INSERT INTO #tempPopulateAdmin
SELECT
*,
NULL
FROM #tempMailWorkItem
SELECT
@AdminWorkQuery = 'INSERT INTO #tempAdminWorkQueue
SELECT W.* FROM #tempPopulateAdmin W,Account A ' + @WhereClause + @OrderClause
PRINT @AdminWorkQuery
EXECUTE (@AdminWorkQuery)
PRINT 'AdminWorkQuery table has all the workitems for admin ordering by evaldate descending'
SELECT
@PageWhereClause = @PageWhereClause + 'WHERE SeqNum > ' + CONVERT(char, @StartRecordNum)
SELECT
@PaginationQuery = 'select * from
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as SeqNum,* FROM #tempAdminWorkQueue) P ' + @PageWhereClause
EXECUTE (@PaginationQuery)
DROP TABLE #tempPopulateAdmin
DROP TABLE #tempMailWorkItem
DROP TABLE #tempAdminWorkQueue
SET NOCOUNT OFF
SET ANSI_WARNINGS ON
END
October 9, 2017 at 1:54 pm
There's an awful lot of dynamic SQL going on there, and I don't have time to try and make sense of that much query today, so let me at least pass this along. It looks a lot like a catch-all query, given the number of parameters. Catch-all queries rarely perform well, and you're already in that boat, so no need to belabor that. I'd suggest you also check what you posted, as the code wouldn't run successfully on your system, as you start a comment with /* and don't appear to finish that anywhere, so there's no way for anyone here to know what's supposed to be commented out and what's not.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 9, 2017 at 5:49 pm
It's not possible to troubleshoot such a stored procedure without the correct information. Please see the second link in my signature line below under "Helpful Links" for the right way to post a performance problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply