Performance issue with Query

  • Hi all,

    I have attached a query that is taking some 1 min to return 30 records. Earlier it was taking even more time, but after I introduced CTE, its performance increased from earlier 2-3 mins to now 1 min. This execution time is still not good and I need to optimize it further. There are no index or execution plan issues with this query.

    I put this query here to see if any of you can suggest some rewriting or anything else to optimize this query.

    Any help will be highly appreciated.

    Regards

  • can you post the actual execution plan please.

    quick scan thro your code....

    case when

    1 = 1

    and 1 = 0

    and 0 = 0

    and wo.status in (0, 1, 31, 41, 62, 54, 62, 5, 36)

    and (wo.sequence - 1) = 0

    and j.job_seeker_type != 2

    not sure what this means????

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • This is generated output. Actual script is like

    case when

    :condition = 1

    and :flag = 0

    and :count = 0

    Don't have execution plan due to some limitations.

  • T.Ashish (4/15/2016)


    This is generated output. Actual script is like

    case when

    :condition = 1

    and :flag = 0

    and :count = 0

    Don't have execution plan due to some limitations.

    You're seriously asking people to look at that long query, with no query plan, and offer guesses about ways to improve the query?

    Even when you have admitted that the query is system-generated?

    If it's system generated, presumably you would be unable to change it?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • The file for that single query is 6.5KILOBYTES!! I have spent a dozen or more hours many times the past trying to decipher and fix stuff like that at clients.

    Sorry, but I think this request goes way beyond what you should expect from a free forum.

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

  • T.Ashish (4/15/2016)


    There are no index or execution plan issues with this query.

    How have you decided your statement above is correct?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • What the heck is this doing in the JOIN criteria:

    0 = 1

    and pbu.bu_id is not null

    I gave up after that. I'm not paid enough.

    Instead of NOLOCK on umpti-gazillion tables, why not just SET READUNCOMMITTED? It'll do the same thing (you'll get just as many duplicate and missing rows in other words).

    ----------------------------------------------------
    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • T.Ashish (4/15/2016)


    Don't have execution plan due to some limitations.

    Then how do you know that there are no "execution plan issues"?

  • Just one thing I saw:

    case

    when 1 = 1 and -- Always true

    1 = 0 and -- Always false

    0 = 0 and -- Always true

    wo.status in (0, 1, 31, 41, 62, 54, 62, 5, 36) and -- Depends on data

    (wo.sequence - 1) = 0 and -- Depends on data

    j.job_seeker_type != 2 -- depends on data

    then '-' -- will never be returned due to 1 = 0 above

    else cn.name -- will always be returned due to 1 = 0 above

    end as supplier_name

    cn.name as supplier_name -- equivalent to above CASE

  • Lynn Pettis (4/15/2016)


    Just one thing I saw:

    case

    when 1 = 1 and -- Always true

    1 = 0 and -- Always false

    0 = 0 and -- Always true

    wo.status in (0, 1, 31, 41, 62, 54, 62, 5, 36) and -- Depends on data

    (wo.sequence - 1) = 0 and -- Depends on data

    j.job_seeker_type != 2 -- depends on data

    then '-' -- will never be returned due to 1 = 0 above

    else cn.name -- will always be returned due to 1 = 0 above

    end as supplier_name

    cn.name as supplier_name -- equivalent to above CASE

    Just last month I made a first onsite visit with a new client for a performance review and one of my major findings was that a very similar "template-driven-query-system" was causing them extreme pain and it simply must be eliminated.

    Once more I get to relay one of my most important Guruism's: Anything that allows developers to slap together code more quickly is inversely proportional to the performance you will get from said code. :w00t:

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

  • With the liberal use of NOLOCK, it is quite likely that this query will return incorrect results. Make sure the query does what it should do first, then worry about performance.

    Now perhaps you value speed over correctness. In that case, try this rewrite:

    SELECT 42 AS TheAnswer;

    (Probably just as incorrect as your current query but a lot faster!)

    Oh, and if you wanted serious help, then give us what we need to help you. Simplify the nonsense out of the query before posting. Test the simplified query as well, who knows it might help. Definitely include the actual execution plan. "Don't have execution plan due to some limitations" does not cut it - in that case the only honest answer we can give you is "Don't have a clue how to optimize this due to some limitations".

    This is a forum for help. Unpaid help. Nobody here is paid to help you. We do so because we like to educate people, but you have to put in an effort.

    Perhaps that is not something you want to do. Perhaps you just want someone to tune your query for you. Nothing wrong with that - but that is called consultancy, starts with negotiotions and a contract, and ends with an invoice. The good thing about that is that, as part of the contract, you can have the consultant sign an NDA, which might make it easier to share details. The bad thing is that you hire just a single consultant, and you cannot judge whether (s)he is good enough - to judge that, you'd have to be better. Over here, many people look at your problem. But you still have to put in most of the work. TANSTAAFL.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • T.Ashish (4/15/2016)


    This execution time is still not good and I need to optimize it further. There are no index or execution plan issues with this query.

    I'm thinking that's a serious contradiction. 😉

    If you want performance help, you need to provide performance related information. Please read and heed the article and the second link in my signature line under "Helpful Links".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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