Super fast SQL Batch Updates

How to avoid slow SQL updates when updating many rows of data.

If you have ever needed to update 12000 rows of data in a database, using 12,000 individual updates, you probably had to wait quite a while for that update to run. Let’s say your update was something like:

UPDATE people set changeDate = GetUTCDate(), firstName = 'Sally Mae' where firstName = 'Sally'
UPDATE people set changeDate = GetUTCDate(), firstName = 'John Bob' where firstName = 'John'
UPDATE people set changeDate = GetUTCDate(), firstName = 'Billy Bob' where firstName = 'Billy'
... (and 11,997 more rows)

You were smart and create a script or some code to generate your 12,000 lines of updates so congrats for that, but running them takes FOREVER!

My friend Paul showed me this method of doing updates and it is incredible how much faster this batch update runs:

UPDATE p
Set changeDate = GetUTCDate(), firstName = newFirstName
FROM (
SELECT newFirstName = 'Sally Mae', oldFirstName = 'Sally' UNION ALL
SELECT newFirstName = 'John Bob', oldFirstName = 'John' UNION ALL
-- ... (and 11,997 more rows)
SELECT newFirstName = 'Billy Bob', oldFirstName = 'Billy' 
) subQ 
JOIN people p on p.firstName = subQ.oldFirstName 

What is the above doing?

First you can think of it creating a temporary result set like this called ‘subQ’:

newFirstName oldFirstName
Sally May Sally
John Bob John
Billy Bob Billy

Then it joins subQ to the people table on the subQ.oldFirstName = people.firstName. Then it updates each row of that joined set in a single action rather than 12,000 separate actions.

How do I update multiple tables at the same time?

Taking the above example a step further, let’s say that while your updating everyone’s name, you also want to update their zipcodes which is stored in an Address table tied to the person table with the person.AddressId foreign key.

You can add it in to the same query like this so it all runs in a single action:

UPDATE p
Set p.changeDate = GetUTCDate(), p.firstName = newFirstName, a.zipcode = newZipCode
FROM (
SELECT newFirstName = 'Sally Mae', oldFirstName = 'Sally', newZipCode = '11111' UNION ALL
SELECT newFirstName = 'John Bob', oldFirstName = 'John', newZipCode = '11111'
-- ... (and 11,997 more rows)
UNION ALL SELECT newFirstName = 'Billy Bob', oldFirstName = 'Billy', newZipCode = '11111' 
) subQ 
JOIN people p on p.firstName = subQ.oldFirstName
JOIN address a on a.AddressId = p.AddressId

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s