Row-By-Row Processing Without Cursor

Question

Row-By-Row Processing Without Cursor

Viewing 15 posts - 31 through 45 (of 70 total)

You must be logged in to reply to this topic. Login to reply

John Beggs SSCommitted Points: 1921 More actions · Answer 1

All of this seems like a lot of work to replace a cursor in a row by row process is needed.

Why is it that people don't seem to understand that cursors are only bad if you use them where a set based solution could have been used?

What's more, while I have no issue with using a table variable or temp table when needed, I challenge you to make either of them out perform a properly formed cursor. FAST_FORWARD anyone?

Idea Deadbeat SSC-Addicted Points: 416 More actions · Answer 2

If the end goal isn't immediate row by row processing - don't forget about the SQL 2005 OUTPUT clause that can be used with INSERT, UPDATE and DELETE.

kevin mann Hall of Fame Points: 3894 More actions · Answer 3

what's the difference betweren FAST_FORWARD and FORWARD_ONLY STATIC READ_ONLY? I have no clue why I have always overlooked FAST_FORWARD.

John Beggs SSCommitted Points: 1921 More actions · Answer 4

FAST_FORWARD is FORWARD_ONLY and READ_ONLY together.

David.Poole SSC Guru Points: 75897 More actions · Answer 5

I have seen cursors out-perform a non-cursor method of looping through records.

The key is knowing exactly when a cursor is necessary or alternatively, when some nifty set based SQL will do the job.

I you come from a procedural language background then cursors feel more natural to use. They are easy to understand.

If your background is set based then you will be more comfortable using some other alternative.

I find the main problem with cursors is when someone writes on that has multiple joins on many tables. This causes all sorts of locking issues. The better solution is to bounce the records you need out to a temp table and loop through the temp table.

The caveat to the above is where you have to protect against changes while the cursor is running.

Perhaps this thread represents an opportunity for a SSC competition. Set a problem that seems to demand a cursor and see if someone can come up with a set bases solution.

LinkedIn Profile

Dmytro Andriychenko SSC Enthusiast Points: 181 More actions · Answer 6

Hi,

I think some people might be missing the point of this article. The author has just suggested a very graceful alternative to using a cursor when bulding some set of statements for example. Cursors have their use, no doubt! In many places SQL Server offers a multitude of ways to do the same thing - one just has to exercise common sence and issues of performance and security when choosing the most appropriate. It certainly does not hurt to know the choices. I for one did not know one can aggreagate string fields in this way - have already found a use for the idea in my work!

Thank you to the author, great stuff!

Kindest regards,

Dmytro

Alexander Kuznetsov SSCrazy Points: 2217 More actions · Answer 7

I think it's a horrible idea, should be mush slower than a cursor in most cases, and more sophisticated.

Dan Smith-306792 SSC Enthusiast Points: 159 More actions · Answer 8

Another approach would be to use functions instead of a procedure in the trigger. For example:

create table Books (

BookCode varchar(5),

BookDesc varchar(100))

go

-- The function processes a new BookCode during insertion

create function fnBookProcess (

@BookCode varchar(5)

)

returns varchar(200)

as

begin

-- Do something useful with each BookCode.

-- Just return a message

return('Function is processing... ' + @BookCode)

end

go

-- trigger runs a select calling the fnBookProcess for each inserted row on the Books table

create trigger tr1 on Books after insert as

print 'Trigger is calling fnBookProcess'

select dbo.fnBookProcess(BookCode) from Inserted

go

-- insert books for testing

insert Books

select 'A','Book desc 1' union all

select 'B','Book desc 2' union all

select 'C','Book desc 3'

Here the fnBookProcess function is called for each line of the Inserted virtual table and it avoids the 8000 varchar limit. The only draw back is that now the insert statement returns a recordset from the trigger's select statement. I don't know how to avoid that.

Jose-cin SSC-Addicted Points: 487 More actions · Answer 9

Hello from Peru 😀 ,

How can i apply this if I'm using sql server 2000? (because the problem of the variable @sql =varchar(8000), in sql server 2000 doesnt exist the data type varchar(max)).

I have the same problem of Dan because idont want to get a result when I'm going to call the function, there is an option to dont get the result of the sentence "select dbo.fnBookProcess(BookCode) from Inserted"?.

Thanks

Jose Gutierrez

PD: (Sorry about my english :S).

José Miguel Gutiérrez Hilaccama
Database Developer - MCTS SQL Server 2005
No way Jose? ... bah

Dan Smith-306792 SSC Enthusiast Points: 159 More actions · Answer 10

Sorry, I just realized that user defined functions can't make any changes to data, so insert, update and delete statements can't be called from within a function.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 11

shashi kant (12/7/2006)
Instand of using this thing u can use the Table variable while loop which help lot compare to Cursor...

Actually, that's an old wive's tale... a "Firehose" cursor is just as fast as a temp table and a While loop... both use TempDB and neither will lock source resources, either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 12

jose (12/6/2007)
Hello from Peru 😀 ,
How can i apply this if I'm using sql server 2000? (because the problem of the variable @sql =varchar(8000), in sql server 2000 doesnt exist the data type varchar(max)).
I have the same problem of Dan because idont want to get a result when I'm going to call the function, there is an option to dont get the result of the sentence "select dbo.fnBookProcess(BookCode) from Inserted"?.
Thanks
Jose Gutierrez
PD: (Sorry about my english :S).

First, you shouldn't do this... you should write the correct set-based code...

Second, if you really must do this because you can't think of the necessary set-based code... then use more than 1 variable to hold the dynamic SQL. Then execute it like this...

EXEC (@SQL1+@SQL2+@SQL3).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ion Freeman Right there with Babe Points: 731 More actions · Answer 13

The whole reason I don't write articles like this is because I don't want to do the heavy lifting around collecting metrics. So, I'd like to see that this is actually faster or less resource intensive than a cursor for realistic uses.

SQLGuy64 Ten Centuries Points: 1081 More actions · Answer 14

I do row by row processing by using a do while loop for example:

declare @LoadedKey BIT

SET @LoadedKey = NULL

SET @LoadedKey =

(

SELECT

TOP 1 ColA

FROM

BusinessTable

WHERE

LOADED =

(

SELECT

IDCol

FROM

BusinessTable

WHERE

FlagCol = 0 AND

ERROR_CODE ='0'

ORDER BY IDCol)

WHILE @LoadedKey IS NOT NULL

BEGIN

--BusinessLogic

SET @LoadedKey =

(

SELECT

TOP 1 ColA

FROM

BusinessTable

WHERE

LOADED =

(

SELECT

IDCol

FROM

BusinessTable

WHERE

FlagCol = 0 AND

ERROR_CODE ='0'

ORDER BY IDCol

)

CONTINUE

END

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 15

Ion Freeman (12/28/2007)
The whole reason I don't write articles like this is because I don't want to do the heavy lifting around collecting metrics. So, I'd like to see that this is actually faster or less resource intensive than a cursor for realistic uses.

There's no difference between a properly formed "Firehose" cursor (Fast Foward) and a While loop except that the cursor is easier to use... 😉

The real key is to avoid the loop for processing sets of data altogether. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)