Looping over records without a Cursor?
-
Hi,
Is it possible to loop over a query result, without loading it into a cursor and stepping over it one record at a time?
I need to perform at least two updates for each row, and these days people seem to be so against cursors......
eg
select * from somwhere
for each row...
update ...
update ...
Thanks,
-
Go thru this link,
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=529
It uses table variable & While loop to handle this scenario..... I would suggest to go for this only if ur record count is small.... or u should be 100% IN MEMORY, provided there is sufficient RAM (Data Cache) available.. It provides No more contention, no more tempdb locks, no more disk I/O!
Cursor uses tempDB & table variable uses MEMORY... for further infm check below link,
http://www.eggheadcafe.com/articles/20010823.asp
-
I think that under some conditions the number of rows to be updated should weigh into the decision to use memory tables.
I agree that if the table size is small, the memory table makes sense. I have used this myself and it works fine. In this case, the number of rows updated, makes no difference either.
If the table is large, say for example, a several hundred thousand rows with large row lengths, and concurrent processes, then using mem table will most likely create problems.
If the table is large, and every row is going to receive two updates, you probably will have to use the "antiquated", and yet efficient method of cursors.
If the table is large but only a few rows will recieve the update then you could, again, use the mem table because you could load your data into it, do the updates there and finally replace the selected rows into the base table.
Ben
-
Why not look at the 'problem' from the other side?
You have n rows in one table, and based on those you want to update two other tables... What about doing all that with just two updates - one update for each table, but updating all qualifying rows at the same time..?
No mess with loops, cursors etc, just two 'simple' set oriented updates
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply