Post reply

Update statement - query performance

  • June 8, 2012 at 3:43 pm

    #262078

    I have a update statement which takes more than 1 minute but if i do select statement with same where clause it took less than 10 seconds...Not sure why the update statement is taking so long....Is it because i have too many indexes on those tables??....

    Does having case statement in where clause hinders the performance of query??

    I tried to analyse execution plan, which had some Key Lookup's and RID lookups but i just don't want to include all the columns from key lookups inside include statement(as there are close to 6 columns)

    I have attached execution plan

    Thanks

  • June 8, 2012 at 3:51 pm

    #1498963

    Drop the NOLOCK hints, they will be ignored anyway as this is an UPDATE.

  • June 8, 2012 at 3:53 pm

    #1498964

    Also, this looks like an estimated execution plan, the actual execution plan would be much more helpful.

  • June 8, 2012 at 4:04 pm

    #1498969

    Here is the actual execution plan

  • June 9, 2012 at 8:24 am

    #1499018

    Sandy2704 (6/8/2012)

    Here is the actual execution plan

    I'm not able to open this after saving it. Are you sure this is a 2005 execution plan?

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

  • June 9, 2012 at 9:02 am

    #1499023

    BTW. There are some serious performance problems to be had with UPDATE in T-SQL if you don't do certain things correctly. I can't, however, tell if the problems are actually present by looking at the execution plan only. Any chance of you posting the actual code for the UPDATE?

    --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 6 posts - 1 through 5 (of 5 total)

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