Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to optimize query


Need to optimize query

Author
Message
sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
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




-----------------------------------------------------------------------------
संकेत कोकणे
Attachments
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17585 Visits: 32262
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
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 ..

-----------------------------------------------------------------------------
संकेत कोकणे
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17585 Visits: 32262
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
Thanks Grant .. I will check it out

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search