Need to optimize query

  • 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

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

  • 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[/url]

  • 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

  • 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

  • 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 ..

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

  • 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

  • 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