none
How to optimize Update query for 10 Million Records in SQL RRS feed

  • Question

  • Hi,

    Trying to update 5 columns (Passing column name through function) in one table. Table contain 10 million records it is running for more than 2 hours and keep on running in SQL Server.

    Please find the below update statement

    UPDATE TEST

    SET NAME = dbo.FunctionValidCheck(Name,'FirstName'),

    LastName = dbo.FunctionValidCheck(LastName,'LastName')

    Please share me your experience or how we can handle it.

    Thanks,

    LuckyAbd

    Tuesday, October 24, 2017 1:28 AM

All replies

  • Hi Lucky,

    Is there no WHERE clause on your UPDATE? This means that every row will get read, functions executed and row updated. 

    You can reduce the work by

    1. Putting a WHERE clause on to restrict the number of updated records (and records read and functions executed)
    2. If the output from the function can be equal to the column, it is worth putting a WHERE predicate (function()<>column) on your update. SQL Server will "update" a row, even if the new value is equal to the old value. Note, be careful of NULLs; NULL will never NOT = a value.
    3. What's in your Functions? Is it just character manipulation, or does it have database calls within the function? If there are database calls inside the function, they will tend to be executed for every row in TEST. Which means you can probably write the same query without functions and SQL optimiser will have a chance to optimise it.
    4. Are there indexes on NAME or LastName? If so, you will probably find it faster to drop the indexes, UPDATE, then rebuild the index.

    If you can include the function scripts, it would help us support you.

    Also, you might include a SHOWPLAN for the UPDATE. This can help us determine where the time is spent.

    Hope that helps,


    Richard

    Tuesday, October 24, 2017 4:12 AM
  • Two things which should be avoided if possible

    1. Put a filter based on your business rule if you can . Otherwise it will do an update on every row of the table (even if its not intended!).

    2. Try to avoid the function call if possible. Scalar functions can prove to be a costly affair for large datasets. If possible, please explain what you're doing inside the functions.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, October 24, 2017 4:46 AM
  • You know a scalar udf is a performance killer and must be avoided, btw what does the udf do? Formatting?

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

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

        SET @x = @x + 10000
    END

    Make sure that ID column has a clustered index 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

    Tuesday, October 24, 2017 5:41 AM
    Answerer
  • Hi LuckyAbd,

    Or you can try batch update. Something like:

    SELECT * 
    INTO #SOD
    FROM AdventureWorks2014.Sales.SalesOrderDetail
    
    select * from #SOD
    
    WHILE (2 > 1) 
      BEGIN 
        BEGIN TRANSACTION 
        UPDATE TOP ( 10000 ) #SOD 
        SET    UnitPriceDiscount = 0.08, 
               ModifiedDate = CONVERT(DATETIME,CONVERT(CHAR(10),getdate(),112)) 
        WHERE  ModifiedDate < CONVERT(DATETIME,CONVERT(CHAR(10),getdate(),112)) 
         
        IF @@ROWCOUNT = 0 
          BEGIN 
            COMMIT TRANSACTION 
             BREAK 
          END 
        COMMIT TRANSACTION 
        -- 1 second delay
        WAITFOR DELAY '00:00:01'
      END -- WHILE
    GO

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 24, 2017 7:08 AM