Yukon SQL Update Performance

An interesting observation on Yukon SQL, sometimes an update statement’s performance is quite slow.  For example, let’s present a SQL table with 3+ million rows with a LogAuditID column , int data type, value of 0 for all columns; the table contains a nonclustered index not on the LogAuditID column.  If I wanted to update all of the rows within this table with a value of 1, running the following statement:
 
update [table] set LogAuditID = 1
 
is quite slow. On both single proc and multi-proc boxes, this one simple statement can take a long time to run.
 
But, if you re-write the same statement (taking advantage of the fact that LogAuditID originally is 0), you can run the following statement:
 
update [table] set LogAuditID = LogAuditID + 1
 
This statement will take ~4:15 (mm:ss) on a single proc box (faster on a multi-proc box).
 
Even shorter still, you can specifically indicate to the SQL Server engine to not use the existing indices when performing the audit:
 
update a set LogAuditID = 1 from [table] a with (index(0))
This particular statement runs in ~3:30 (mm:ss).

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