Post reply

Optimize update with an index

  • August 22, 2016 at 7:57 pm

    #311389

    Hi Guys ,

    I am wondering how to optimize this below update statement as it probably cause table scan :

    Update Table1

    Set a=@a , b = @b-2 , c =@c

    Where id =@id and name=@name , file =@file , lastupdated = @lastupdated

    Should I create index on column in where criteria -> non clustered index on id, name and lastupdated

    Any feedback are much appreciated

    Thank you

  • August 22, 2016 at 8:02 pm

    #1896791

    What is the PK on this table?

    _____________
    Code for TallyGenerator

  • August 22, 2016 at 9:40 pm

    #1896799

    Thank you for the reply mate 🙂

    No PK in this table . Why do you ask this question ?

  • August 23, 2016 at 4:57 am

    #1896848

    The only way to know what's happening now and how adding an index will change things is to look at the execution plan before and after and test it.

    Based on what you'very said thus far, you will get a scan and adding an index on those 4 columns will help.

    Do you have a clustered index? If so, on what columns?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • August 23, 2016 at 5:12 am

    #1896853

    WhiteLotus (8/22/2016)

    Thank you for the reply mate 🙂

    No PK in this table . Why do you ask this question ?

    Is there a clustered index on the table?

    What proportion of rows are likely to be affected by the update?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 23, 2016 at 7:47 pm

    #1897078

    Thanks for the feedback mate

    No index in table at all ( I click on index folder and nothing comes out )

    Actually this is deadlock case as there are multiple updates statement running on same table so I am thinking if I can add the missing index it will help .

    In testing can I run multiple updates ( on same table but different row ) simultaneously ?

    Thank you

  • August 24, 2016 at 1:59 am

    #1897115

    WhiteLotus (8/23/2016)

    Thanks for the feedback mate

    No index in table at all ( I click on index folder and nothing comes out )

    Actually this is deadlock case as there are multiple updates statement running on same table so I am thinking if I can add the missing index it will help .

    In testing can I run multiple updates ( on same table but different row ) simultaneously ?

    Thank you

    Without any indexes, SQL Server has no alternative but to scan the whole table checking each row to see if it qualifies for the update. Let's ignore for the moment that there may be a valid reason why the table doesn't have a clustered index and move on to creating an index specifically to optimise the update statement you've posted.

    The index structure will depend upon usage and will also depend upon the specificity of the columns (a column containing only 'Y' or 'N' is low specificity, a unique column is high).

    If you're running singleton updates then you want the index to provide the fastest possible way to the row.

    If you're updating a bunch of rows in the same statement then you want those rows to be adjacent in the index.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 15, 2016 at 11:30 pm

    #1901265

    Thank you:)

Viewing 8 posts - 1 through 7 (of 7 total)

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