|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Fetch statementI need to update few records in a very large table and wanted to know the
pros and cons of using a fetch statement in a stored procedure to first select all the records that need to be updated and then loop through each row and update accordingly... verses updating the rows using the update statement directly. What advantage/disadvantage exists in either technique in regard to locks, Performance,etc...? Many thanks Shahriar Set-based statements are generally more efficient than cursors in their
use of locking and resources and there are plenty of other good reasons not use cursors. Unless you find otherwise in some particular situation, assume the UPDATE is the right solution. 99% of the time you won't need a cursor. -- David Portas SQL Server MVP -- What are some good reasons of not using cursors?
In this particular case that I was asking about...the situation is as follow.. I have over 700,000 records in my table where only about 4-5 records need being updated. I thought by first pulling out the records using a select statement, I would find the key for each record and then issue an update directly based on the key verses having it done directly in one Update statement. Thanks Shahriar Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1121433335.681591.300580@g43g2000cwa.googlegroups.com... > Set-based statements are generally more efficient than cursors in their > use of locking and resources and there are plenty of other good reasons > not use cursors. Unless you find otherwise in some particular > situation, assume the UPDATE is the right solution. 99% of the time you > won't need a cursor. > > -- > David Portas > SQL Server MVP > -- > > What are some good reasons of not using cursors? Inefficient (most of the time). Harder to re-use the code. Code is lessportable. Code is generally longer, more complex and harder to test and maintain than the set-based alternatives. Good SQL developers rarely write cursors and will be much more productive without them. Cursors often conceal database design / specification errors that would otherwise be more visible using set-based code. > I thought by first pulling out the records using a select Performing the UPDATE for each row in a cursor individually is much> statement, I would find the key for each record and then issue an update > directly based on the key verses having it done directly in one Update > statement. worse than a single UPDATE. Plus you have more work to populate the cursor first. Why not try it out? If you find a situation where think the cursor is better then get a second opinion. Almost always there is a better way. -- David Portas SQL Server MVP -- If you only have 4 - 5 rows to update, it will be a lot simpler to code a
single UPDATE statement as opposed to the 10 or so lines to code a cursor solution. If you're dealing with 4 - 5 updates of rows that are otherwise unrelated, you might have to copy + paste + edit the one UPDATE statement 4 times. Still a lot simpler and more efficient than the cursor. "Shahriar" <HelloShahr***@hotmail.com> wrote in message news:XPOBe.3$JJ.0@trnddc09...Show quote > What are some good reasons of not using cursors? > > In this particular case that I was asking about...the situation is as > follow.. > > I have over 700,000 records in my table where only about 4-5 records need > being updated. I thought by first pulling out the records using a select > statement, I would find the key for each record and then issue an update > directly based on the key verses having it done directly in one Update > statement. > > Thanks > Shahriar > > > > > "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message > news:1121433335.681591.300580@g43g2000cwa.googlegroups.com... >> Set-based statements are generally more efficient than cursors in their >> use of locking and resources and there are plenty of other good reasons >> not use cursors. Unless you find otherwise in some particular >> situation, assume the UPDATE is the right solution. 99% of the time you >> won't need a cursor. >> >> -- >> David Portas >> SQL Server MVP >> -- >> > > Avoid using cursors as much as you can. SQL Server is a rdbms, It was
designed to give the best performance when you use set-based solutions. Performance Tuning SQL Server Cursors http://www.sql-server-performance.com/cursors.asp AMB Show quote "Shahriar" wrote: > I need to update few records in a very large table and wanted to know the > pros and cons of using a fetch statement in a stored procedure to first > select all the records that need to be updated and then loop through each > row and update accordingly... verses updating the rows using the update > statement directly. What advantage/disadvantage exists in either technique > in regard to locks, Performance,etc...? > > Many thanks > Shahriar > > >  |
|||||||||||||||||||||||
Other interesting topics