SQL Server 2008 bulk update using stored procedure

1
votes

I have 2 tables in the DB. each with "Name" column and "Count" column each.

I would like to update the Count column in the second table from the Count in the first table only where the "Name" columns are equal.

Example:

First Table:

Name Count

jack 25
mike 44

Name Count

jack 23
mike 9
david 88

Result (the second table would look like that...)

Name Count

jack 25
mike 44
david 88

NOTES:
1. Both tables are huge. (although the second table is bigger...)
2. The update must be as fast as possible...
(if there are more options other than stored procedures, i would love to hear.)
3. "Count" defined as bigint while "Name" as nvarchar(100)
4. the "Count" field in the first table is always bigger than the equivalent in the
second table.

I think that there are more options (other than stored procedure) maybe with MERGE or TRANSACTION as long as it will be the fastest way...

Thanks!

1
sql-server-2008stored-proceduresbulk
What is the exact definition of the tables? Include the exact column types, clustered index definition, all non-clustered indexes, all constraints. And also define 'huge'. Some consider 1M huge, some consider it puny. – Remus Rusanu
hi! in the second table the "Name" column is clustered indexed Ascending. the first table has between 100K-400K rows while the second has between 10M-100M rows. – Michael

1 Answers

2
votes

The best way would be to keep it simple

UPDATE Table2
SET Count = t1.Count
FROM Table1
WHERE Table2.Name = Table1.Name 
  AND Table2.Count <> Table1.Count

If the performance of this query is not satisfactory due to the size of your tables the best solution would be to partition the tables based on the name field. The query can then be run from different threads at the same time with and extra filter based on Name to satisfy the partition function.

For example: (assuming name is a varchar(20) column)

UPDATE Table2
SET Count = t1.Count
FROM Table1
WHERE Table2.Name = Table1.Name 
  AND Table2.Count <> Table1.Count
  AND Table2.Name between cast('Jack' as varchar(20)) 
                      and cast('Mike' as varchar(20))

(The cast on the strings is a big help for Sql Server to properly do partition elimination.)