CODEWITHSUNDEEP
sqlsql-serverperformance
Corez
Corez

Reputation: 354

Optimize Update query across tables

I'm running on Sql Server 2014. I'm trying to perform the following query:

UPDATE [dbo].[Table1]
   SET Table1.[PaymentId] = ST.PaymentId
      ,Table1.[ServiceId] = ST.ServiceId
      ...several more fields
 FROM [dbo].[StagingTable] as ST
 WHERE 
   Table1.Id = ST.Id

Table1 has an identity and index on Id but StagingTable does not have an index for Id. I left out the index on StagingTable because I need to do a very fast bulk insert of data. I have about 4 million records in each table.

This query appears to be running very slow because it's doing a table scan for StagingTable and a Custered Index Seek for Table1 initially in the execution plan. However, I'd like to sort by Id in StagingTable and then the execution should be a Clustered Index Scan rather than a seek.

Does anyone know how to do this in an update query? I'm doing this as part of an SSIS job so I'm trying to do it all within one query.

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

After you load the data in to StagingTable, then create an index:

create index idx_stagingtable_id on stagingtable(id);

This will not slow down the bulk load and it will make this go much faster.

Upvotes: 1

Related Questions