locked
Enhance a SQL query with update of millions of rows RRS feed

  • 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

     
    • Edited by Kalman Toth Monday, December 30, 2013 9:03 AM Spelling
    Monday, December 30, 2013 8:40 AM

Answers

All replies