Enhance a SQL query with update of millions of rows

Question

Hi ,

I have this query developed to updated around 200 million of rows on my production , I did my best but please need your recommendations\concerns to make it more enhanced

DECLARE @ORIGINAL_ID AS BIGINT
      SELECT FID001 INTO #Temp001_ 
      FROM INBA004 WHERE RS_DATE>='1999-01-01' 
      AND RS_DATE<'2014-01-01' AND CLR_f1st='SSLM'
      and FID001 >=12345671 
      
      WHILE (SELECT COUNT(*) FROM #Temp001_ ) <>0
      BEGIN
      
            SELECT TOP 1 @ORIGINAL_ID=FID001 FROM #Temp001_  ORDER BY FID001
            
            PRINT CAST (@ORIGINAL_ID AS VARCHAR(100))+' STARTED'
            
            SELECT DISTINCT FID001 
            INTO #OUT_FID001
            FROM OUTTR009 WHERE TRANSACTION_ID IN (SELECT TRANSACTION_ID FROM 
            INTR00100 WHERE FID001 = @ORIGINAL_ID)
            
            UPDATE A SET RCV_Date=B.TIME_STAMP 
            FROM OUTTR009 A INNER JOIN INTR00100 B
            ON A.TRANSACTION_ID=B.TRANSACTION_ID
            WHERE A.FID001 IN (SELECT FID001 FROM #OUT_FID001)
            AND B.FID001=@ORIGINAL_ID
            
            UPDATE A SET Sending_Date=B.TIME_STAMP 
            FROM INTR00100 A INNER JOIN OUTTR009 B
            ON A.TRANSACTION_ID=B.TRANSACTION_ID
            WHERE A.FID001=@ORIGINAL_ID
            AND B.FID001 IN (SELECT FID001 FROM #OUT_FID001)
            
            DELETE FROM #Temp001_  WHERE FID001=@ORIGINAL_ID 
            
            DROP TABLE #OUT_FID001
            PRINT CAST (@ORIGINAL_ID AS VARCHAR(100))+' FINISHED'
      END

 

Answer 1

Here is a large update sample using batching:

http://www.sqlusa.com/bestpractices2005/hugeupdate/

---

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 2

DECLARE @x INT
SET @x = 1
WHILE @x < 44,000,000  -- Set appropriately
BEGIN

    UPDATE Table SET a = c+d where ID BETWEEN @x AND @x + 10000

    SET @x = @x + 10000
END

Make sure that ID column has a CI on.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 3

Here is a large update sample using batching:

http://www.sqlusa.com/bestpractices2005/hugeupdate/

---

This is a nice and helpful trick!

Answer 4

DECLARE @x INT
SET @x = 1
WHILE @x < 44,000,000  -- Set appropriately
BEGIN

    UPDATE Table SET a = c+d where ID BETWEEN @x AND @x + 10000

    SET @x = @x + 10000
END

This is neat!