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).