SQLServerCentral / Discuss Content Posted by Amin Sobati / Article Discussions / Article Discussions by Author / Row-By-Row Processing Without Cursor / Latest Posts

RE: Row-By-Row Processing Without Cursor

DragonGod — Tue, 15 Sep 2009 07:29:28 GMT

[quote][b]Jeff Moden (9/14/2009)[/b][hr][quote][b]Roy Oliver (9/14/2009)[/b][hr][quote][b]Jeff Moden (9/12/2009)[/b][hr][quote][b]Roy Oliver (9/12/2009)[/b][hr]Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.[/quote]Why? They are inherently RBAR. "For Each Row".[/quote]True, yet isn't the performance faster than the SQL Server Cursor?[/quote]I don't know, Roy. Comparing anything in Oracle with SQL Server is pretty hard to do. A better question might be, are they faster than an Oracle Cursor? ... and the answer is "I don't know for sure in Oracle" because I've never tested triggers vs set-based code in Oracle, but I don't believe so. I can say I have tested Cursors vs Set-Based in Oracle... properly written set-based code blows cursors away even in Oracle. It's pretty much a myth that Oracle has been "optimized" for cursors so far as speed is concerned.Most RDBMS's work best with Set-Based code... it would be a real shame if they changed the current set-based mechanism built into SQL Server triggers into similar RBAR code as they did in Oracle if for no other reason other than to simply NOT get into the habit of writting RBAR anywhere.[/quote]Yeah, that's a good point.Thanks

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Mon, 14 Sep 2009 21:05:53 GMT

[quote][b]Roy Oliver (9/14/2009)[/b][hr][quote][b]Jeff Moden (9/12/2009)[/b][hr][quote][b]Roy Oliver (9/12/2009)[/b][hr]Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.[/quote]Why? They are inherently RBAR. "For Each Row".[/quote]True, yet isn't the performance faster than the SQL Server Cursor?[/quote]I don't know, Roy. Comparing anything in Oracle with SQL Server is pretty hard to do. A better question might be, are they faster than an Oracle Cursor? ... and the answer is "I don't know for sure in Oracle" because I've never tested triggers vs set-based code in Oracle, but I don't believe so. I can say I have tested Cursors vs Set-Based in Oracle... properly written set-based code blows cursors away even in Oracle. It's pretty much a myth that Oracle has been "optimized" for cursors so far as speed is concerned.Most RDBMS's work best with Set-Based code... it would be a real shame if they changed the current set-based mechanism built into SQL Server triggers into similar RBAR code as they did in Oracle if for no other reason other than to simply NOT get into the habit of writting RBAR anywhere.

RE: Row-By-Row Processing Without Cursor

DragonGod — Mon, 14 Sep 2009 08:18:49 GMT

[quote][b]Jeff Moden (9/12/2009)[/b][hr][quote][b]Roy Oliver (9/12/2009)[/b][hr]Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.[/quote]Why? They are inherently RBAR. "For Each Row".[/quote]True, yet isn't the performance faster than the SQL Server Cursor?

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Sat, 12 Sep 2009 16:41:30 GMT

[quote][b]Roy Oliver (9/12/2009)[/b][hr]Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.[/quote]Why? They are inherently RBAR. "For Each Row".

RE: Row-By-Row Processing Without Cursor

DragonGod — Sat, 12 Sep 2009 15:52:42 GMT

Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

RE: Row-By-Row Processing Without Cursor

Jack Corbett — Tue, 02 Sep 2008 15:12:45 GMT

[quote][b]Jeff Moden (1/2/2008)[/b][hr]The [i]real [/i]point here is that you should not be using [i]any [/i]form of RBAR in a trigger... no matter how you do it, calling a RBAR proc from a trigger is an insane thing to do... the proc should be rewritten to handle sets of data instead of the slothful agony of single row processing. ;)[/quote]Jumping in here way late, but there is no doubt that RBAR processing whether a cursor or while loop in a trigger is a very bad idea. You are better off staging the data and processing it in a separate process whether that's Service Broker or another option.Something I do not recall seeing mentioned is that you can do a Select Into temp_table from inserted\deleted then access that temp table in a stored procedure called from the trigger where you can do some set-based processing, of course that's only necessary if you want to encapsulate the code so you can re-use it because you can process it the same way in the trigger.

RE: Row-By-Row Processing Without Cursor

RBarryYoung — Sun, 04 May 2008 13:21:44 GMT

[quote][b]Jeff Moden (5/2/2008)[/b][hr]Funny, they work fine for me... they take me right to the post I wanted.[/quote]I think that it has to do with paging. Anchors, like page#postnumber won't work if the topic spans multiple pages and the target post is not on the first page. Since "Posts per Page" is customizable, it might be paging differently for us.

RE: Row-By-Row Processing Without Cursor

poornima.s_pdi — Sat, 03 May 2008 01:06:10 GMT

Hi, Your post satisfied my need.Once again 'Thank u' for ur timely help.

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Fri, 02 May 2008 18:54:01 GMT

[quote][b]poornima.s_pdi (5/1/2008)[/b][hr]Hi Jeff, Thanks a lot for ur reply.I learnt more from ur sites.very good snd useful forum...[/quote]Thank you for the feed back, Poornima... are you all set now?

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Fri, 02 May 2008 18:52:29 GMT

Funny, they work fine for me... they take me right to the post I wanted.

RE: Row-By-Row Processing Without Cursor

RBarryYoung — Fri, 02 May 2008 10:32:33 GMT

[quote][b]Jeff Moden (4/30/2008)[/b][hr]See the following URL... almost identical situation...[url]http://www.sqlservercentral.com/Forums/Topic491969-149-1.aspx#bm492576[/url][/quote]Jeff: FYI, the "Post #" links like this do not seem to be working, they just take to the beginning of the Topic. When I use the Post-links on the lower left-hand side of the posts, it gives me something like this: [url]http://www.sqlservercentral.com/Forums/FindPost492576.aspx[/url] which seems to work better.

RE: Row-By-Row Processing Without Cursor

poornima.s_pdi — Thu, 01 May 2008 23:03:22 GMT

Hi Jeff, Thanks a lot for ur reply.I learnt more from ur sites.very good and useful forum...

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Wed, 30 Apr 2008 09:10:14 GMT

See the following URL... almost identical situation...[url]http://www.sqlservercentral.com/Forums/Topic491969-149-1.aspx#bm492576[/url]

RE: Row-By-Row Processing Without Cursor

poornima.s_pdi — Wed, 30 Apr 2008 00:04:35 GMT

Hi, s i need row by row process only. For eg-----Consider the table studrno name dept1 ram cse2 radha eceConsider another table studSubjsdno rno mark1 sub 578 1 80 Maths 579 1 98 PhysicsHere i want to duplicate the rows of stud and using tat newly generated rno i have to update the same records in studSubj table.i.e.,In stud table we have rno-1,for rno-1 we have 2 records in studSubj.If i generate duplicate records then it will have some rno like 4 r 5..For rno-4 i need to have same records of rno-1 .This is my entire need.

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Tue, 29 Apr 2008 23:46:29 GMT

Ummm, then why did you post that you wanted to process rows one at a time? We've all said that's a bad thing... what is it that you're trying to do?

RE: Row-By-Row Processing Without Cursor

poornima.s_pdi — Tue, 29 Apr 2008 23:38:54 GMT

Hi Ion, I got answer for my query through this forum.In this already Shaalini had posted thread about this.Jeff had given the answer.Ur site s vey useful and immediate reply from ur site make us to learn more.Thanks a lot...

RE: Row-By-Row Processing Without Cursor

Ion Freeman — Tue, 29 Apr 2008 07:02:34 GMT

Poornima, I think the basic concept is to avoid doing row by row operations when you can -- you should reflect on your need to do this. But, if you want a row by row operation and you don't like cursors, you can just dump your query result into a temporary table with an AUTOID column and increment a counter in your loop, taking a new record from your temporary table each time. You can even fancy it up with a CTE and ROW_NUMBER, if you're using SQL Server 2005. Ion

RE: Row-By-Row Processing Without Cursor

poornima.s_pdi — Tue, 29 Apr 2008 01:02:19 GMT

Hi, Tell me the way to retrieve records row by row without using Cursor Concept.I dont like fetching record using cursor.Best solution plz.

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Wed, 02 Jan 2008 21:34:32 GMT

The [i]real [/i]point here is that you should not be using [i]any [/i]form of RBAR in a trigger... no matter how you do it, calling a RBAR proc from a trigger is an insane thing to do... the proc should be rewritten to handle sets of data instead of the slothful agony of single row processing. ;)

RE: Row-By-Row Processing Without Cursor

shashi kant — Wed, 02 Jan 2008 06:06:51 GMT

I agree with Matt Whitfield while loop concept is more eassy then using cursor and this method is good in conceptually but not feasible practically when you are dealing with VLDB's RegardsShashi Kant Chauhan

RE: Row-By-Row Processing Without Cursor

Matt Whitfield — Wed, 02 Jan 2008 03:40:14 GMT

I have to say, this 'solution' is actually worse than having a cursor. It would have the same performance implications (because the reason cursors are 'bad' is because row based processing is slow).Therefore it's just another way to cause the same problem, and is a badly researched article. The reason it's worse than a cursor is because of the 8000 character limit, which will cause large insert batches to fail.Nice.

RE: Row-By-Row Processing Without Cursor

Ian Yates — Sun, 30 Dec 2007 19:09:56 GMT

I agree with Jeff - set-based is certainly best. The idea of using service broker in the background is pretty good. Often the rbar stuff, if absolutely necessary, could be done later, in which case service broker (which I've never actually used) or populating a batch table with a batchID and the record keys and having a timed sql server agent job to process the rows is good enough.Often such things arise on the server because it's "easier" than writing a little service or app to do the processing, particularly if it's a simple small (and will stay that way) job that runs once a day. RBAR in a trigger though sounds like it could happen a LOT and thus a better set-based approach is warranted... I would be interested in seeing the performance metrics of such code in a trigger comparing cursors calling a stored proc vs building the dynamic SQL and calling a stored proc. The proc would have to do something nasty like write a text file to disk :) The proc could be called 1000 times due to 1000 rows being inserted into the table on which the trigger is based. We could pretend the file being written to disk has the artibrary ID as its filename and the contents of the rest of the table row within it for some other system to collect....I'm too lazy today though to code it up :)

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Sat, 29 Dec 2007 08:29:04 GMT

[quote][b]John Beggs (12/8/2006)[/b][hr]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?[/quote]I absolutely agree... my problem with most people's code is they make it so that you MUST use RBAR. Instead of working out a set based solution, they'll have some RBAR GUI code laying around that's written to handle precisely 1 row. Then, they'll use that same single row proc for a batch process of thousands of rows instead of having a "sister" process that will handle all of the rows in the batch.For control loops where each loop processes [i]sets [/i]of rows instead of RBAR, I see no problem with using a Fast_Forward cursor or a Temp/While Loop. Even then, though, people still seem to think that certain things just aren't possible using set-based code and they inappropriately revert to ISAM/RBAR thinking.

RE: Row-By-Row Processing Without Cursor

ian.stone — Sat, 29 Dec 2007 02:45:54 GMT

Problem with code like this is the max variable length will be hit you when you least expect it. There is also the bigger problem of a delimiter or separator appearing in the data which will cause the stored procedure to throw up. Users are good at this!.I have one example which currently has 177000 rows in the insert(ed) table which uses code like this:DECLARE @MaxRowCount int, @RowNumber int/*Create table to hold data from the cursors select statementYou could check for there being only one row to process here and skip the table creation code. Experience has shown it's not worth the grief.*/CREATE TABLE #SomeTableName (RowNumber int IDENTITY (1,1),..........)--Insert the rows from the cursors select statementINSERT INTO #SomeTableName (............) SELECT .......... FROM SELECT @MaxRowCount=MAX(RowNumber) FROM #SomeTableNameSET @RowNumber=1WHILE @RowNumber<=@MaxRowCount BEGIN--do your processing SELECT ................. FROM #SomeTableName WHERE RowNumber=@RowNumber SET @RowNumber=(@RowNumber+1) ENDSurprisingly code like this really flies. Don’t use variable tables as you will be back at square one!

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Fri, 28 Dec 2007 16:26:52 GMT

What would you have the cursor or While loop do in a test? I think they'll come out the same but I'm not sure so, you know me, I'll do a test. I just need to know what the code should do because I'm so set based oriented, I'm not sure I could come up with a decent test for a cursor vs While loop example...

RE: Row-By-Row Processing Without Cursor

David.Poole — Fri, 28 Dec 2007 15:59:17 GMT

I'm not sure about that Jeff.I have come across situations where the firehose cursor is faster than the loop.I pinned it down to the loop running many separate queries where as the firehose cursor grabs a lock and blasts through the records happily blocking other users while it does so.Works fine on small datasets but as you say, if there is a set based way of achieving the same result use the set based way.

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Fri, 28 Dec 2007 12:02:03 GMT

[quote][b]Ion Freeman (12/28/2007)[/b][hr]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.[/quote]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. :D

RE: Row-By-Row Processing Without Cursor

Andy Brons — Fri, 28 Dec 2007 10:51:07 GMT

I do row by row processing by using a do while loop for example:declare @LoadedKey BITSET @LoadedKey = NULLSET @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 NULLBEGIN --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 )CONTINUEEND

RE: Row-By-Row Processing Without Cursor

Ion Freeman — Fri, 28 Dec 2007 08:08:00 GMT

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.

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Fri, 28 Dec 2007 00:24:37 GMT

[quote][b]jose (12/6/2007)[/b][hr]Hello from Peru :D , 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 GutierrezPD: (Sorry about my english :S).[/quote]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).

RE: Row-By-Row Processing Without Cursor

Jeff Moden — Fri, 28 Dec 2007 00:13:41 GMT

[quote][b]shashi kant (12/7/2006)[/b][hr]Instand of using this thing u can use the Table variable while loop which help lot compare to Cursor...[/quote]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.

RE: Row-By-Row Processing Without Cursor

Dan Smith-306792 — Tue, 11 Dec 2007 15:12:49 GMT

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.

RE: Row-By-Row Processing Without Cursor

Jose-cin — Thu, 06 Dec 2007 09:52:05 GMT

Hello from Peru :D , 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 GutierrezPD: (Sorry about my english :S).

RE: Row-By-Row Processing Without Cursor

Dan Smith-306792 — Tue, 04 Dec 2007 08:38:37 GMT

Another approach would be to use functions instead of a procedure in the trigger. For example:[code]create table Books (BookCode varchar(5),BookDesc varchar(100))go-- The function processes a new BookCode during insertioncreate function fnBookProcess ( @BookCode varchar(5))returns varchar(200)asbegin -- Do something useful with each BookCode. -- Just return a message return('Function is processing... ' + @BookCode)endgo-- trigger runs a select calling the fnBookProcess for each inserted row on the Books tablecreate trigger tr1 on Books after insert as print 'Trigger is calling fnBookProcess' select dbo.fnBookProcess(BookCode) from Insertedgo -- insert books for testinginsert Books select 'A','Book desc 1' union all select 'B','Book desc 2' union all select 'C','Book desc 3'[/code]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.

RE: Row-By-Row Processing Without Cursor

Alexander Kuznetsov — Tue, 04 Dec 2007 07:30:37 GMT

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

RE: Row-By-Row Processing Without Cursor

Dmytro Andriychenko — Fri, 05 Jan 2007 09:05:00 GMT

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

RE: Row-By-Row Processing Without Cursor

David.Poole — Sun, 10 Dec 2006 06:31:00 GMT

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.

RE: Row-By-Row Processing Without Cursor

John Beggs — Fri, 08 Dec 2006 17:11:00 GMT

FAST_FORWARD is FORWARD_ONLY and READ_ONLY together.

RE: Row-By-Row Processing Without Cursor

kevin mann — Fri, 08 Dec 2006 14:08:00 GMT

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

RE: Row-By-Row Processing Without Cursor

Idea Deadbeat — Fri, 08 Dec 2006 11:03:00 GMT

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.