Home Forums SQL Server 2008 T-SQL (SS2K8) Update statement: use the in clause or run simple update 500 times RE: Update statement: use the in clause or run simple update 500 times

  • August 27, 2015 at 7:43 pm

    #1822950

    chandrika5 (8/7/2015)

    I have to do updates via web app. Stuck with time out:

    Update mytable

    set col1= 'some value',

    col2 = 'some other value',

    where Id in ( ,,,,,,,, ......)

    The Id is primary key, Big Int not null and is Identity.

    The updates are in batches of 500 IDs from the web application. Instead of running the update statement 500 times, I created an IN clause and do the update.

    Now this is timing out.

    What are my optimum options ?

    Thanks,

    What is the web application doing where it needs to update 500 or more rows? How did you determine what the content list of the IN clause is?

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