Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to optimize query Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 12:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
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




-----------------------------------------------------------------------------
संकेत कोकणे


  Post Attachments 
Proc_GetCRFActivityStatusReport.sqlplan (4 views, 237.63 KB)
Post #1412785
Posted Tuesday, January 29, 2013 2:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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/



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1412838
Posted Tuesday, January 29, 2013 3:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1412853
Posted Tuesday, January 29, 2013 3:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1412874
Posted Tuesday, January 29, 2013 5:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
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 ..





-----------------------------------------------------------------------------
संकेत कोकणे
Post #1412951
Posted Tuesday, January 29, 2013 5:42 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
Jeff Moden has a great article 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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1412961
Posted Tuesday, January 29, 2013 5:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
Thanks Grant .. I will check it out

-----------------------------------------------------------------------------
संकेत कोकणे
Post #1412962
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse