Optimization with multiple Select and Update statements

  • Hi,

    If I have multiple select and update statements in my stored procedure, will it be better to group up the select statements follow by the update statements like follows:

    SELECT ...

    SELECT ...

    SELECT ...

    SELECT ...

    UPDATE ...

    UPDATE ...

    UPDATE ...

    UPDATE ...

    Or, will the performance be better if I will to group the SELECT and UPDATE as a pair like follows:

    SELECT ...

    UPDATE ...

    SELECT ...

    UPDATE ...

    SELECT ...

    UPDATE ...

    SELECT ...

    UPDATE ...

  • If the purpose of each SELECT is to be used as the source for an UPDATE, probably you'll be better off doing each SELECT/UPDATE pair together. You'll need to careful of other places in the code that accesses that same tables but in a different order. There you could be hitting deadlocks routinely.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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