none
best practice to update data from last 3 month in a very large Table RRS feed

  • Question

  • Hi Expert,

    i have 2 tables: 
    1. History Table A (10 billion records, non clustered index on all column, the Table is partitioned pro every 6 months) 
    2. Staging Table B (2000 records, no partition)

    i compare table B with the Data of last 3 months of table A, and if the Data from Table A is older, i will update them with the Data from Table B.
    My Update Query look like this:

    UPDATE COLUMN A=COLUMN B
    FROM B JOIN A
    WHERE DATE COLUMN B>DATE COLUMN A


    The Problem is: this Query takes 5 hours to update only 2000 records. The JOIN between Table A and Table B takes too long time.
    i have tried a lot to modify the Query (e.g: add nonclustered Index on Date column...) but there is no better results

    Do you experts have any Idea how to improve the Performance of this Update Query?
    Monday, June 24, 2019 8:33 PM

All replies

  • To help you, we would need to see the query plan.

    However, what you describe is almost always a blocking issue, not a query performance issue.

    Also, I would highly recommend you do this in a loop.

    DECLARE @rowcnt BIGINT = 1000;
    
    WHILE @rowcnt = 1000
    BEGIN
    	UPDATE TOP (1000) COLUMN A=COLUMN B
    	FROM B JOIN A
    	WHERE DATE COLUMN B>DATE COLUMN A
    
    	SET @rowcnt = @@ROWCOUNT;
    END
    * adjust the 1000 value to something reasonable for your update

    Monday, June 24, 2019 9:00 PM
  • Please do not post the same question independently to mutliple forums - Stack Overflow
    Monday, June 24, 2019 9:17 PM