January 29, 2013 at 12:41 am
HI I need to optimize below query ... currently I do not have DDL and index info with me..find below SP and attached Query execution plan .. Please tell me any changes in SP can help to optimize query.
CREATE PROC Proc_GetCRFActivityStatusReport_BABE
@vWorkSpaceId as varchar(1000),
@iPeriod as varchar(10)=null,
@vSubjectId as varchar(1000)=null,
@iParentActivityNodeId as varchar(1000)=null,
@iActivityNodeId as varchar(1000)=null ,
@cSubjectWiseFlag as varchar(5)=null,
@cDataStatus as varchar(10)=null,
@iWorkFlowStageId as varchar(10)=null
AS
BEGIN
IF @cDataStatus=',E,'
BEGIN
SET @cDataStatus=''
END
---------------------------------------------
SELECT
DISTINCT
vMySubjectNo = CASE WHEN ActivityMst.cSubjectWiseFlag='N'
THEN '0000'
ELSE WorkSpaceSubjectMst.vMySubjectNo
END,
parent.vWorkSpaceId,
parent.iNodeId AS parentid,
parent.vNodeDisplayName AS parent,
child.iNodeId as childid,
child = CASE WHEN ISNULL(CRFInfo.iRepeatNo,1) = 1
THEN child.vNodeDisplayName
ELSE child.vNodeDisplayName+'(Repeat-'+CONVERT(VARCHAR(50),CRFInfo.iRepeatNo)+')'
END,
STATUS = CASE WHEN CRFInfo.cDataStatus='F' AND CRFInfo.iWorkFlowstageId=30
THEN 'L'
WHEN (CRFInfo.cDataStatus='E' Or CRFInfo.cDataStatus='D') and CRFInfo.iWorkFlowstageId=20
THEN 'FnlRP'
WHEN (CRFInfo.cDataStatus='E' or CRFInfo.cDataStatus='D') and CRFInfo.iWorkFlowstageId=10
THEN 'SRP'
WHEN CRFInfo.cDataStatus='D' and CRFInfo.iWorkFlowstageId=0
THEN 'FRP'
WHEN CRFInfo.cDataStatus='B' and CRFInfo.iWorkFlowstageId=0
THEN 'DEC'
WHEN CRFInfo.cDataStatus =''
THEN 'DEP'
END,
ISNULL(DCFMst.DCF,0) AS DCF,
parent.iNodeNo,
iMySubjectNo = CASE WHEN ActivityMst.cSubjectWiseFlag='Y'
THEN WorkSpaceSubjectMst.iMySubjectNo
WHEN ActivityMst.cSubjectWiseFlag='N'
THEN 0
END,
child.vActivityId,
parent.iPeriod,
vSubjectId = CASE WHEN ActivityMst.cSubjectWiseFlag='N'
THEN '0000'
ELSE WorkSpaceSubjectMst.vSubjectId
END,
ISNULL(WorkSpaceSubjectMst.cRejectionFlag,'')
FROM WorkSpaceNodeDetail parent
INNER JOIN WorkSpaceNodeDetail child ON
parent.vWorkSpaceId=child.vWorkSpaceId
AND (parent.iNodeId=child.iParentNodeId OR (parent.iNodeId=child.iNodeId and parent.iParentNodeId=1))
AND child.iPeriod=parent.iPeriod
AND parent.cStatusIndi<>'D'
AND child.cStatusIndi<>'D'
INNER JOIN MedExWorkSpaceHdr ON
MedExWorkSpaceHdr.vWorkspaceId=parent.vWorkSpaceId
AND MedExWorkSpaceHdr.iNodeId=child.iNodeId
INNER JOIN ActivityMst ON
ActivityMst.vActivityId=child.vActivityId
AND ActivityMst.cStatusIndi<>'D'
LEFT JOIN WorkSpaceSubjectMst on
child.vWorkSpaceId=WorkSpaceSubjectMst.vWorkspaceId
AND WorkSpaceSubjectMst.iPeriod=child.iPeriod
AND WorkSpaceSubjectMst.cStatusIndi <>'D'
AND ActivityMst.cSubjectWiseFlag = 'Y'
LEFT JOIN
(
SELECT
CRFHdr.iNodeId,
CRFHdr.vWorkSpaceId,
CRFDtl.vSubjectId,
ActivityMst.cSubjectWiseFlag,
CRFHdr.iPeriod,
CRFDtl.iRepeatNo,
CRFDtl.cDataStatus,
CRFDtl.cStatusIndi,
CRFDtl.iWorkFlowstageId,
CRFDtl.nCRFDtlNo
FROM CRFHdr
INNER JOIN ActivityMst ON
ActivityMst.vActivityId = CRFHdr.vActivityId
AND ActivityMst.cStatusIndi<>'D'
INNER JOIN CRFDtl ON
CRFHdr.nCRFHdrNo=CRFDtl.nCRFHdrNo
AND CRFDtl.cStatusIndi<>'D'
AND CRFHdr.cStatusIndi<>'D'
) AS CRFInfo ON
CRFInfo.iNodeId=child.iNodeId
AND CRFInfo.vWorkSpaceId=parent.vWorkSpaceId
AND (WorkSpaceSubjectMst.vSubjectId = CRFInfo.vSubjectId OR CRFInfo.vSubjectId='0000')
AND CRFInfo.iPeriod=parent.iPeriod
LEFT JOIN
(
SELECT
COUNT(nDCFNo) AS DCF,
nCRFDtlNo,
cDCFStatus
FROM DCFMst
GROUP BY nCRFDtlNo,cDCFStatus
) AS DCFMst ON
CRFInfo.nCRFDtlno=DCFMst.nCRFDtlNo
AND DCFMst.cDCFStatus in ('N','O')
WHERE
CHARINDEX(',' + (CAST(parent.vWorkSpaceId AS varchar(1000))) + ',',
CASE WHEN @vWorkSpaceId <> ''
THEN @vWorkSpaceId
ELSE ',' + CAST(parent.vWorkSpaceId as VARCHAR(1000)) + ',' END) > 0
AND ActivityMst.cSubjectWiseFlag=@cSubjectWiseFlag
AND ISNULL(parent.vTemplateId,0)<>'0001'
AND parent.iNodeId<>1
AND parent.cStatusIndi<>'D'
AND child.cStatusIndi<>'D'
AND CASE @cSubjectWiseFlag
WHEN 'Y'
THEN CHARINDEX(',' + (CAST(WorkSpaceSubjectMst.vSubjectId AS varchar(1000))) + ',',
CASE WHEN @vSubjectId <> ''
THEN @vSubjectId ELSE ',' + CAST(WorkSpaceSubjectMst.vSubjectId as VARCHAR(1000)) + ',' END)
ELSE 1 End > 0
AND CHARINDEX(',' + (CAST(parent.iNodeId AS varchar(1000))) + ',',
CASE WHEN @iParentActivityNodeId <> ''
THEN @iParentActivityNodeId
ELSE ',' + CAST(parent.iNodeId as VARCHAR(1000)) + ',' END) > 0
AND CHARINDEX(',' + (CAST(child.iPeriod AS varchar(1000))) + ',',
CASE WHEN @iPeriod <> ''
THEN @iPeriod
ELSE ',' + CAST(child.iPeriod as VARCHAR(1000)) + ',' END) > 0
AND CHARINDEX(',' + (CAST(child.iNodeId AS varchar(1000))) + ',',
CASE WHEN @iActivityNodeId <> ''
THEN @iActivityNodeId
ELSE ',' + CAST(child.iNodeId as VARCHAR(1000)) + ',' END) > 0
AND CHARINDEX(',' + (CAST(isnull(CRFInfo.cDataStatus,'0') AS varchar(1000))) + ',',
CASE WHEN @cDataStatus <> ''
THEN @cDataStatus
ELSE ',' + CAST(ISNULL(CRFInfo.cDataStatus,0) as VARCHAR(1000)) + ',' END) > 0
AND CHARINDEX(',' + (CAST(ISNULL(CRFInfo.iWorkFlowstageId,'0') AS varchar(1000))) + ',',
CASE WHEN @iWorkFlowStageId <> ''
THEN @iWorkFlowStageId
ELSE ',' + CAST(isnull(CRFInfo.iWorkFlowstageId,'0') as VARCHAR(1000)) + ',' END) > 0
ORDER BY iMySubjectNo,parent.iNodeId,child.iNodeId,parent.iNodeNo
END
-----------------------------------------------------------------------------
संकेत कोकणे
January 29, 2013 at 2:40 am
attempt of catch-all query detected!
I would suggest dynamic sql.
Something to read:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
January 29, 2013 at 3:10 am
You might get away with changing this to use a Recursive CTE for the Parent Child Relationship rather than a Join to iterate through the tree.
But I think the same as Eugine this falls into the bounds of a catch all query which the link Eugine posted should help with.
I would relook at the Case Statments with in the CHARINDEX's that are on the where clause, you might benefit from spliting up the strings.
Eg In this
CHARINDEX(',' + (CAST(ISNULL(CRFInfo.iWorkFlowstageId,'0') AS varchar(1000))) + ',',
CASE WHEN @iWorkFlowStageId <> ''
THEN @iWorkFlowStageId
ELSE ',' + CAST(isnull(CRFInfo.iWorkFlowstageId,'0') as VARCHAR(1000)) + ',' END) > 0
it looks like you are searching the String iWorkFlowStateId which is a comma seperated list, for the @iWorkFlowStageId.
There has to be a better way to do this.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 29, 2013 at 3:55 am
Every one of these
CHARINDEX(',' + (CAST(parent.vWorkSpaceId AS varchar(1000))) + ','
Is absolutely going to kill performance. Functions against columns in the WHERE clause requires scans and there is no way around it. Also, CASE statements in the WHERE clause usually lead to very poor performance. You need to eliminate that sort of filtering.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2013 at 5:24 am
Thanks for reply .
is there any chance to replace charindex function.
The SP which I run is
exec Proc_GetCRFActivityStatusReport_BABE ',0000001611,0000001637,0000001460,0000001461,0000001747,0000001751,0000001445,0000001748,0000001877,0000001922,','',',TL11-00012,AH11-00514,TL10-00003,TL10-00004,PA11-00003,SU11-00032,TL10-00001,NA11-00020,SU11-00008,PA11-00001,PA11-00002,SU11-00009,NA12-00001,TL10-00002,SU11-00033,PA11-00005,TL10-00005,AH11-00518,TL11-00013,TL11-00014,AH11-00562,TL10-00006,PA11-00013,SU11-00034,NA12-00002,SU11-00010,SU11-00035,PA11-00014,TL11-00001,AH11-00563,TL11-00017,TL11-00018,AH11-00564,TL11-00002,PA11-00015,SU11-00036,SU11-00037,PA11-00018,TL11-00003,AH11-00565,TL11-00022,TL11-00023,AH11-00566,TL11-00004,PA11-00019,SU11-00084,SU11-00085,PA11-00020,TL11-00005,AH11-00567,TL11-00083,TL11-00085,AH11-00568,TL11-00006,SU11-00086,TL11-00007,AH11-00569,TL11-00092,TL11-00093,AH11-00571,TL11-00008,TL11-00009,AH11-00572,TL11-00094,TL11-00095,AH11-00604,TL11-00010,TL11-00011,AH11-00802,TL11-00103,TL11-00104,AH11-01349,AH11-01559,TL11-00105,TL11-00027,TL11-00028,TL11-00029,TL11-00030,TL11-00031,TL11-00033,TL11-00034,','','','Y','',''
...
In execution plan I can see Hatsh Match
is taking 35% (which is highest) ...All the columns in select statments are included in indexes .
Unfortunatly i do have any sample data,index defination,DDL to show ..
-----------------------------------------------------------------------------
संकेत कोकणे
January 29, 2013 at 5:42 am
Jeff Moden has a great article[/url] on how to use a tally table to break up a comma delimited list.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2013 at 5:42 am
Thanks Grant .. I will check it out
-----------------------------------------------------------------------------
संकेत कोकणे
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply