Using sql update statement instead of cursor

I've seen too many instances where a cursor can be simply rewritten as an update statement.

I was working at a client site where one of their process calls a stored procedure that uses a cursor do some updates.  That stored procedure would run over the weekend to update about 2000 records and bog down the production SQL (You'd think I was joking).  We re-write the stored procedure.  Granted, the update itself is about 50 lines with 5 tables, subqueries, and left outer joins, but we got the stored procedure to perform the exact updates in 35 seconds.

A performance improvement of how many percent?  I don't even know how to compare.

This link only shows you how to update 1 table:  http://www.w3schools.com/sql/sql_update.asp 

You can find more detailed (and better) tutorial here: http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx

The following is a quick reference for myself. 

UPDATE table1

SET table1.field1 = table2.field2

FROM table1, table2, table3

WHERE xxx

Print | posted on Friday, September 11, 2009 10:36 AM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski