InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 06:49:51 GMT20http://www.sqlservercentral.com/Forums/Topic1412785-392-1.aspxThanks Grant .. I will check it outTue, 29 Jan 2013 05:42:17 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1412785-392-1.aspxJeff Moden has a [url=http://www.sqlservercentral.com/articles/T-SQL/62867/]great article[/url] on how to use a tally table to break up a comma delimited list.Tue, 29 Jan 2013 05:42:16 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1412785-392-1.aspxThanks for reply .is there any chance to replace charindex function.The SP which I run is [code="other"]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','',''[/code]...In execution plan I can see Hatsh Matchis 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 .. Tue, 29 Jan 2013 05:24:30 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1412785-392-1.aspxEvery one of these[code="sql"]CHARINDEX(',' + (CAST(parent.vWorkSpaceId AS varchar(1000))) + ','[/code]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.Tue, 29 Jan 2013 03:55:02 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1412785-392-1.aspxYou 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 [code="sql"]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 [/code]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.Tue, 29 Jan 2013 03:10:18 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1412785-392-1.aspxattempt of catch-all query detected!I would suggest dynamic sql.Something to read:[url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]Tue, 29 Jan 2013 02:40:44 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1412785-392-1.aspxHI 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.[code="other"] 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[/code]Tue, 29 Jan 2013 00:41:14 GMTsanket kokane