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!