Updating 20 rows in a table is really slow

Viewed 53

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!

1 Answers

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

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