T-Sql - optimize a stored procedure that is doing an update on a join on large tables

The actual update is as follo开发者_如何学运维ws:

UPDATE TableA 
    SET TableA.Col1 =TableB.Col2 
    FROM TableA (nolock)
        INNER JOIN TableB (nolock) 
            ON TableA.Col1 = -TableB.Col2  

TableA Col1 is being inverted if it is found in TableB Col1 Not all rows in TableA are having Col1 updated, assume 20 to 40%

TableA size is 1 million rows, TableB size is 8 million rows Both columns Col1 and Col2 are indexed. DB is Sql Server 2000 which is not helping the situation.

Thanks,

MAA

I think it wil be a lot faster if you drop the index on tableA.Col1, run the update and then recreate the index on col1.

It updates the index for every single write. Dropping and recreating let's it do that as a bulk operation wich should be much faster.

Also make sure Col1 is not in a clustered index. If it is you may want to change that.

GJ

This is one case where you want to loop but process in batches of records not one at a time. You'll need to find out which bacth size works best in your system but start with 5000 and if if is fast then go to a higher number.

    DECLARE @Min_id INT, @Max_id INT

    SELECT @Min_id= MIN(ID) 
    FROM TableA


    SET @Max_id =@Min_id + 5000

    WHILE @Min_id IS NOT NULL
        BEGIN

            UPDATE TableA 
            SET TableA.Col1 =TableB.Col2 
            FROM TableA (nolock) 
            INNER JOIN TableB (nolock) 
                ON TableA.Col1 = -TableB.Col2 

            SELECT @Min_id= MIN(ID) 
            FROM TableA
            WHERE ID > @Max_id

            SET @Max_id = @Min_id + 5000
        END