Post reply

Update colum and use it in where-statment at same time

  • August 31, 2012 at 12:12 am

    #280671

    I'm using MSSQL Server 2008 R2 and have a fairly large table. It´s wide, about 50 columns and contains about 26 000 000 rows. Until recently an update statement worked ok and ran about 20 min. Now it runs for more than 2 hours.

    update batch col = value

    where (col = 0 or col is null or col = 2000000000)

    and dt between date1 and date2

    and CustomerId in (select CustomerId from Customers where active = 1)

    I have an index on col. Is it bad to update a column used in the where clues?

    I rewrot therewrote like this and got it finished in 20 min again

    select id

    into #tmp

    from batch

    where isnull(col, 0) in (0, 2000000000)

    and dt between date1 and date2

    and CustomerId in (select CustomerId from Customers where active = 1)

    update batch

    set col1 = value

    where id IN (select id from #tmp)

    Is it bad to update a column used in the where-statment

  • August 31, 2012 at 2:08 am

    #1532249

    fredriksv80 (8/31/2012)

    ...I have an index on col. Is it bad to update a column used in the where clues?...

    No, the entire set of rows matching the search predicates are identified before the update takes place.

    A couple of small changes to your script:

    -- avoid using functions on a column which is referenced

    -- in the WHERE clause or in JOINS. Almost always this will

    -- prevent SQL Server from using an index on that column.

    SELECT b.id

    into #tmp

    from batch b

    INNER JOIN Customers c

    ON c.CustomerId = b.CustomerId AND c.active = 1

    WHERE (b.col1 IS NULL OR b.col1 IN (0, 2000000000))

    AND b.dt BETWEEN date1 and date2

    GROUP BY b.ID

    ORDER BY b.ID

    -- assuming ID is PK of table Batch, this should

    -- give you an efficient merge join in the plan

    CREATE UNIQUE CLUSTERED INDEX [ID] ON #tmp (ID)

    UPDATE b SET

    col1 = value

    FROM batch b

    INNER JOIN #tmp t ON t.ID = b.ID

    “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

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

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