SQL Server Performance

Update statement - correlated or not?

Discussion in 'T-SQL Performance Tuning for Developers' started by superboot, Aug 13, 2007.

  1. superboot New Member

    Hi all,
    I am trying to speed up an update statement. I have all the right indexes so I am now looking at the update syntax. I am trying to update value(s) in 1 table from another 'working' table. The table being updated can have up to 12 million rows.
    Here is my current query -
    UPDATE Summary
    SET Column_float = b.Column_float
    FROM Summary a, Summary_calc b
    WHERE a.PK_Col_1 = b.PK_Col_1
    AND a.PK_Col_2 = b.PK_Col_2
    AND a.PK_Col_3 = b.PK_Col_3
    Here is an alternative a colleague suggested, however I believe it is not returning the same results.
    UPDATE Summary
    SET Column_float =
    (SELECT b.Column_float
    FROM Summary_calc b
    WHERE a.PK_Col_1 = b.PK_Col_1
    AND a.PK_Col_2 = b.PK_Col_3
    AND a.PK_Col_3 = b.PK_Col_3)
    FROM Summary a
    Is there something I am missing with the 2nd query? Are there any alternatives to query 1?
    Thanks in advance
    Darcy
  2. ndinakar Member

    Looks like its the number of rows to be updated thats killing the query rather than the actual update statement. See if you can do the update in batches. If there is a PK in the table perhaps you can divide the update over a range of PK's for each batch.
  3. satya Moderator

    Also you could look at the transaction log contention in this regard, due to the large update the log may be waiting to increase the size may also affect the performance. Performing update in smaller batches will always have better performance than any other process.
  4. martins New Member

    The main difference between the 2 queries are that the first one will only update records in the Summary table where the join with the Summary_calc table succeeds. The second query will update ALL rows in the Summary table. Depending on your exact scenario this might indeed return different results.
  5. superboot New Member

    Hi all,
    Thanks for your feedback :)
    I have found that the results for the 2 statements is producing different results - this concerns me with respect to update 2. I am in the process of creating a single primary key as the joins I am making involve up to 10 columns - I expect this to make a big difference.
    Will keep you posted.

Share This Page