Post reply

Query Performance Issue with multiple unions and a view

  • March 28, 2016 at 7:42 am

    #1867967

    chandan_jha18 (3/28/2016)

    Dear All,

    Today while running the procedure in its original form, which takes around 10 seconds to output 100 rows for a particular set of parameters, I opened a new query window and ran the following:

    select * from sys.dm_os_waiting_tasks where session_id=my spid.

    This was giving waits on 'CXPACKETS' . I tried to interpret the resource_description field which had similar values:

    exchangeEvent id=Pipec9dabf000 WaitType=e_waitPipeGetRow nodeId=134

    exchangeEvent id=Pipec9dabf000 WaitType=e_waitPipeGetRow nodeId=134

    Out of a bad habit of using maxdop hint when seeing such wait types, I put a hint in the procedure with MAXDOP(0) and the query is able to return results within 1 second.

    So I have two possible solutions at this point:

    - Use a permanent cache table to populate the data from view every 15 or 30 minutes(takes 5-10 seconds) and refer that in place of view in the procedure code

    - Use maxdop hint.

    Sorry for the long break, we had a major festival last week:-)

    Regards

    Chandan

    All the MAXDOP hint is doing is allowing it to consume as many resources as it wants. If it used all CPUs for a full second, would that still be desirable? Probably not.

    The cache thing is a good short term solution but I'd still be digging into the code and the related indexes.

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

  • March 28, 2016 at 8:16 am

    #1867979

    Jeff: "All the MAXDOP hint is doing is allowing it to consume as many resources as it wants. If it used all CPUs for a full second, would that still be desirable? Probably not.

    The cache thing is a good short term solution but I'd still be digging into the code and the related indexes."

    I am really sorry for my mistake here. I wanted to type MAXDOP(1) hint that I used to get the performance to the point where I wanted it.

    single threaded operation, but you are right Sir. I will keep trying to focus on the original query to find out the possible solution.

Viewing 2 posts - 16 through 16 (of 16 total)

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