Query Optimization: Rewriting UPDATE statements

Query optimization is the most common practice when it comes to database performance improvement. Most of the common optimization techniques relates to the SELECT operation such as Indexing, avoiding (Select *), avoiding DISTINCT operator, using sub queries or temp tables, etc.. But when it comes to UPDATE DML operation, there are not many optimization techniques available except to include WHERE conditions whenever possible to reduce the impacted rows volume.

When faced with SQL UPDATE slowness, we had to opt for an alternate solution explained below. We were dealing with huge FACT tables (Indexed). In order to update or populate data for the metric columns in these tables, there were several UPDATE statements. Since these were huge tables, UPDATE operations were very slow despite using the relevant Indexed columns in WHERE conditions. That’s when we decided to break the UPDATE operation into below steps bringing down the overall execution time.

1. Load the updated output to a temp table
2. Truncate original table
3. Recreate the original table from temp table

Load the updated output to a temp table: The first step in this process was to create a temporary table dynamically that will hold all data from parent table along with the updated data (columns) as well. This is a faster operation compared to Update (row by row operation) because it is a Select operation and can benefit from using Indexed columns in the Where clause or in JOIN conditions.

Truncate original table: Next step is to empty or truncate the original table

Recreate the original table from temp table: Finally, insert data from temporary table back to original table.

Sample:
Original Update Statement:
UPDATE TABLE1
SET
Column1 = 'abc',
Column2 = 'xyz'
WHERE
IDX_Column = 'AAA';
Three step alternate solution:
1) 
SELECT
'abc' as Column1,
'xyz' as Column2,
Column3,
Column4,
Column5,
...
ColumnN 
INTO tempTable 
FROM
TABLE1
WHERE
IDX_Column = 'AAA';
2)
TRUNCATE TABLE TABLE1;
3)
INSERT INTO TABLE1
SELECT * FROM tempTable;

Conclusion: When dealing with large table updates which is usually a row by row operation and especially when the queries involve joins and where conditions with Indexes the above approach will help in bringing down the overall execution time drastically.