Updating 20 rows in a table is really slow

I can’t figure out why updating only 21 rows in a table takes so much time.

Step 1: I’m creating #tempTable from the StagingTable (it will never have more than 20 rows of data)

CREATE TABLE #tmpTable (
    ID INT NULL,
    UniqueID INT NULL,
    ReportDate VARCHAR(15) NULL,
    DOB Datetime NULL,
    Weight VARCHAR(15) NULL,
    Height VARCHAR(15) NULL)

INSERT INTO #tempTable (
    ID,
    UniqueID,
    ReportDate,
    DOB,
    Weight,
    Height)

SELECT 
    A.ID,
    A.UniqueID,
    A.ReportDate,
    A.DOB,
    A.Weight,
    A.Height
FROM [testDB].[StagingTable] as A
WHERE A.UniqueID = '12345'

Step 2. Updating FinalTable:

UPDATE [Customers].[FinalTable]
SET ID = B.ID,
    UniqueID = B.UniqueID,
    ReportDate = B.ReportDate,
    DOB = B.DOB,
    Weight = B.Weight,
    Height = B.Height
FROM #tempTable AS B
WHERE [Customers].[FinalTable].[ReportDate] = B.ReportDate
AND [Customers].[FinalTable].[DOB] = B.DOB

This query takes more than 30 minutes! Is there any way to speed up this update process? Any ideas what I might be doing wrong?

I just want to add that the FinalTable has millions of rows…

Any help would be greatly appreciated. Thanks!

Answer

If there are only 30 matches, then you want an index on #temptable(ReportDate, DOB):

create index idx_temptable_2 on #temptable(ReportDate, DOB);

Advertisement

Answer