Query Performance Tuning

  • Hi All,

    I would like to get some help regarding optimizing the below query. Is there anything very obvious in the below query which can be improved? i would not be able to post DDL & DML;

    Any help would be greatly appreciated.

    SELECT w1.ASSIGNMENT_ID, w1.WORKSHEET_ID, w1.EFFECTIVE_DATE, w1.APPROVED_BY, w3.CREATED_BY

    FROM WORKSHEET_PAYROLL_VW w1

    INNER JOIN WORKSHEET w3 ON w3.WORKSHEET_ID = w1.WORKSHEET_ID

    WHERE w1.EFFECTIVE_DATE

    BETWEEN

    CASE WHEN @p_MOST_RECENT_ONLY = 'Y' THEN

    (SELECT MAX(w2.EFFECTIVE_DATE)

    FROM WORKSHEET_PAYROLL_VW w2

    WHERE w1.ASSIGNMENT_ID = w2.ASSIGNMENT_ID AND

    (ISNULL(@p_WKS_EFFECTIVE_DATE,w2.EFFECTIVE_DATE<=w2.EFFECTIVE_DATE))

    ELSE ISNULL(@p_WKS_EFFECTIVE_DATE,w1.EFFECTIVE_DATE)

    END

    AND

    CASE WHEN @p_MOST_RECENT_ONLY = 'Y' THEN

    (SELECT MAX(w2.EFFECTIVE_DATE)

    FROM WORKSHEET_PAYROLL_VW w2

    WHERE w1.ASSIGNMENT_ID = w2.ASSIGNMENT_ID AND

    (ISNULL(@p_WKS_TO_EFFECTIVE_DATE,w2.EFFECTIVE_DATE)>=w2.EFFECTIVE_DATE))

    ELSE ISNULL(@p_WKS_TO_EFFECTIVE_DATE,w1.EFFECTIVE_DATE)

    END

  • Well, the highly conditional Where clause will probably cause an overly broad execution plan to be built.

    Can you separate each of those CASE clauses into separate procs and have the parent proc pick which one to call based on the parameter value? That often helps in those cases.

    Based on the names, I'm assuming some of the objects in the From clause are views intead of tables. That often causes query slowdown, because views are usually overbuilt for their use. Would be worth checking into. Do they include tables/columns that aren't needed for this query? Do they nest to other views?

    Beyond that, without DDL or at least an execution plan, no, it just looks like a query. Not having those things makes tuning into guesswork.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The view has many columns which are not used in the query. I tried creating index on the view but received a message saying:

    Cannot create index because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.

  • Views are tricky to index. Lots of rules on that.

    The better bet is to write out the query you really need in this, instead of using views.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • consider this,

    Original query

    update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid=@WMPID

    update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid in

    (select workmgmtplanid from workmgmtlinkedplans with(nolock) where linkedworkmgmtplanid=@WMPID)

    update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid in

    (select linkedworkmgmtplanid from workmgmtlinkedplans with(nolock) where workmgmtplanid=@WMPID)

    Modified query

    update workmgmtplan set IsWebPlannerTracked=1 where planid in

    (

    Select @WMPID

    Union

    select workmgmtplanid from workmgmtlinkedplans with(nolock) where linkedworkmgmtplanid= @WMPID

    Union

    select linkedworkmgmtplanid from workmgmtlinkedplans with(nolock) where workmgmtplanid= @WMPID

    )

    Is it possible to replace UNION with CASE expression to obtain the same result?

  • Replace "Union" with "Union All", and you should have a decent query there. Not sure what you mean about replacing Union with Case statements.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thank You all for your opinion and insight. It turned out that there was a function call in where clause which was slowing down the query. I removed the function call and added a pre-query to fix this issue.

  • Can you say which function call it was in the where clause and how you fixed it? Not sure I see it in your OP statement - or was it in the view(s)?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Yes it was in the View.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply