How can I efficiently do a database massive update?

Question

I have a table with some duplicate entries. I have to discard all but one, and then update this latest one. I've tried with a temporary table and a while statement, in this way:

CREATE TABLE #tmp_ImportedData_GenericData
(
    Id int identity(1,1),
    tmpCode varchar(255)  NULL,
    tmpAlpha3Code varchar(50)  NULL,
    tmpRelatedYear int NOT NULL,
    tmpPreviousValue varchar(255)  NULL,
    tmpGrowthRate varchar(255)  NULL
)

INSERT INTO #tmp_ImportedData_GenericData
SELECT
    MCS_ImportedData_GenericData.Code, 
MCS_ImportedData_GenericData.Alpha3Code,
MCS_ImportedData_GenericData.RelatedYear,
MCS_ImportedData_GenericData.PreviousValue,
MCS_ImportedData_GenericData.GrowthRate
FROM MCS_ImportedData_GenericData
INNER JOIN
(
    SELECT CODE, ALPHA3CODE, RELATEDYEAR, COUNT(*) AS NUMROWS
    FROM MCS_ImportedData_GenericData AS M
    GROUP BY M.CODE, M.ALPHA3CODE, M.RELATEDYEAR
    HAVING count(*) > 1
) AS M2 ON MCS_ImportedData_GenericData.CODE = M2.CODE
    AND MCS_ImportedData_GenericData.ALPHA3CODE = M2.ALPHA3CODE
    AND MCS_ImportedData_GenericData.RELATEDYEAR = M2.RELATEDYEAR
WHERE
(MCS_ImportedData_GenericData.PreviousValue <> 'INDEFINITO')

 -- SELECT * from #tmp_ImportedData_GenericData
 -- DROP TABLE #tmp_ImportedData_GenericData

DECLARE @counter int
DECLARE @rowsCount int

SET @counter = 1

SELECT @rowsCount =  count(*) from #tmp_ImportedData_GenericData
-- PRINT @rowsCount

WHILE @counter  < @rowsCount
BEGIN
    SELECT 
        @Code = tmpCode, 
        @Alpha3Code = tmpAlpha3Code, 
        @RelatedYear = tmpRelatedYear, 
        @OldValue = tmpPreviousValue, 
        @GrowthRate = tmpGrowthRate 
    FROM 
        #tmp_ImportedData_GenericData
    WHERE 
        Id = @counter

    DELETE FROM MCS_ImportedData_GenericData 
    WHERE 
        Code = @Code 
        AND Alpha3Code = @Alpha3Code  
        AND RelatedYear = @RelatedYear  
        AND PreviousValue <> 'INDEFINITO' OR PreviousValue IS NULL  

    UPDATE 
        MCS_ImportedData_GenericData 
        SET 
          PreviousValue = @OldValue, GrowthRate = @GrowthRate 
    WHERE 
        Code = @Code 
        AND Alpha3Code = @Alpha3Code  
        AND RelatedYear = @RelatedYear  
        AND MCS_ImportedData_GenericData.PreviousValue ='INDEFINITO'

    SET @counter = @counter + 1
END

but it takes too long time, even if there are just 20000 - 30000 rows to process.

Does anyone has some suggestions in order to improve performances?

Thanks in advance!

Answer 1

WITH q AS (
        SELECT  m.*, ROW_NUMBER() OVER (PARTITION BY CODE, ALPHA3CODE, RELATEDYEAR ORDER BY CASE WHEN PreviousValue = 'INDEFINITO' THEN 1 ELSE 0 END)
        FROM    MCS_ImportedData_GenericData m
        WHERE   PreviousValue <> 'INDEFINITO'
        )
DELETE
FROM    q
WHERE   rn > 1

Answer 2

Quassnoi's answer uses SQL Server 2005+ syntax, so I thought I'd put in my tuppence worth using something more generic...

First, to delete all the duplicates, but not the "original", you need a way of differentiating the duplicate records from each other. (The ROW_NUMBER() part of Quassnoi's answer)

It would appear that in your case the source data has no identity column (you create one in the temp table). If that is the case, there are two choices that come to my mind:
1. Add the identity column to the data, then remove the duplicates
2. Create a "de-duped" set of data, delete everything from the original, and insert the de-deduped data back into the original

Option 1 could be something like... (With the newly created ID field)

DELETE
   [data]
FROM
   MCS_ImportedData_GenericData AS [data]
WHERE
   id > (
         SELECT
            MIN(id)
         FROM
            MCS_ImportedData_GenericData
         WHERE
            CODE = [data].CODE
            AND ALPHA3CODE = [data].ALPHA3CODE
            AND RELATEDYEAR = [data].RELATEDYEAR
        )

OR...

DELETE
   [data]
FROM
   MCS_ImportedData_GenericData AS [data]
INNER JOIN
(
   SELECT
      MIN(id) AS [id],
      CODE,
      ALPHA3CODE,
      RELATEDYEAR
   FROM
      MCS_ImportedData_GenericData
   GROUP BY
      CODE,
      ALPHA3CODE,
      RELATEDYEAR
)
AS [original]
   ON [original].CODE = [data].CODE
   AND [original].ALPHA3CODE = [data].ALPHA3CODE
   AND [original].RELATEDYEAR = [data].RELATEDYEAR
   AND [original].id <> [data].id

Answer 3

I don't understand used syntax perfectly enough to post an exact answer, but here's an approach.

Identify rows you want to preserve (eg. select value, ... from .. where ...)

Do the update logic while identifying (eg. select value + 1 ... from ... where ...)

Do insert select to a new table.

Drop the original, rename new to original, recreate all grants/synonyms/triggers/indexes/FKs/... (or truncate the original and insert select from the new)

Obviously this has a prety big overhead, but if you want to update/clear millions of rows, it will be the fastest way.