SQL Server : Optimizing Update Queries for Large Data Volumes-Oracle
Updating very large tables can be a time taking task and sometimes it might take hours to finish. In addition to this, it might also cause blocking issues.Here are few tips to optimize the updates on large data volumes.
[*] Removing index on the column to be updated.
[*] Executing the update in smaller batches.
[*] Disabling Delete triggers.
[*] Replacing Update statement with a Bulk-Insert operation.
With that being said, let’s apply the above points to optimize an update query.
The code below creates a dummy table with 200,000 rows and required indexes.
01CREATETABLEtblverylargetable 02( 03snoINTIDENTITY, 04col1 CHAR(800), 05col2 CHAR(800), 06col3 CHAR(800) 07) 08GO 09DECLARE@i INT=0 10WHILE( @i < 200000 ) 11BEGIN12INSERTINTOtblverylargetable 13VALUES('Dummy', 14Replicate('Dummy', 160), 15Replicate('Dummy', 160)) 16SET@i=@i + 1 17END18GO19CREATEINDEXix_col1 20ONtblverylargetable(col1) 21GO 22CREATEINDEXix_col2_col3 23ONtblverylargetable(col2) 24INCLUDE(col3) Consider the following update query which is to be optimized. It’s a very straight forward query to update a single column.
1UPDATEtblverylargetable 2SETcol1 = 'D'3WHEREcol1 = 'Dummy' The query takes 2:19 minutes to execute.
Let’s look at the execution plan of the query shown below. In addition to the clustered index update, the index ix_col1 is also updated. The index update and Sort operation together take 64% of the execution cost.
http://www.sqlservergeeks.com/Assets/Uploaded-CMS-Files/Update_QryPlan_WithIndex-66f16a20-1b55-4721-95d9-3b104189623f.jpg
1. Removing index on the column to be updated
The same query takes 14-18 seconds when there isn’t any index on col1. Thus, an update query runs faster if the column to be updated is not an index key column. The index can always be created once the update completes.
2.Executing the update in smaller batches
The query can be further optimized by executing it in smaller batches. This is generally faster. The code below updates the records in batches of 20000.
1DECLARE@i INT=1 2WHILE( @i
页:
[1]