I include it here because it allows us to compare the cost of context-switches to the cost of updates.DECLARE CURSOR c1 IS SELECT * FROM test6; rec_cur c1%rowtype; BEGIN OPEN c1; LOOP FETCH c1 INTO rec_cur; EXIT WHEN c1%notfound; UPDATE test SET fk = rec_, fill = rec_WHERE pk = rec_cur.pk; END LOOP; CLOSE C1; END; / This is the simplest PL/SQL method and very common in hand-coded PL/SQL applications.Here are few tips to SQL Server Optimizing the updates on large data volumes. 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 can always be created once the update completes. Executing the update in smaller batches The query can be further optimized by executing it in smaller batches. The code below updates the records in batches of 20000. When updating in batches, even if the update fails or it needs to be stopped, only rows from the current batch are rolled back. Disabling Delete triggers Triggers with cursors can extremely slow down the performance of a delete query.
Thus, an update query runs faster if the column to be updated is not an index key column.
The UPDATE portion of the code works in an identical fashion to the Implicit Cursor Loop, so this is not really a separate "UPDATE" method as such.
The interesting thing about this method is that it performs a context-switch between PL/SQL and SQL for every FETCH; this is less efficient.
Finally the following stored procedure is created which will accept the Data Table as parameter and then will insert all records into the table that are not present in the table and the one that already exists will be updated.
The below Stored Procedure can be used where in the SQL Server version 2005 where MERGE function is not supported.