SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Moving away from a cursor to while loop
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vmessy
Starting Member

1 Posts

Posted - 08/24/2009 :  16:15:29  Show Profile  Reply with Quote
Hi all,

Im trying to write a query which will output a row at a time. I have managed to do this using a cursor however I would prefer to use a while loop as i understand that cursors are bad practice.

My query currently gets a list of members for a specified date range and then for each member in the date range pulls out the following info:

1. membership details such as name, age
2. payment details such as card, cash, account
3. order totals
4. outstanding debt

the extract has to be in the order above. Using a cursor i have managed to do this. Im happy to paste the code but its pretty long with a load of case statements.

I looked on the net for some good while loop examples but cant find anything good for sql 2005 which includes row by row for more then one sql select statement (1,2,3,4 are individual select statements) where the membership is used in each where clause.

Any one recommend any good sites or similar while samples?

Thanks

vmessy

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 08/24/2009 :  16:25:21  Show Profile  Reply with Quote
WHILE loop vs. a CURSOR may not buy you much (or anything). The "bad practice" is really any looping at all when compared with non-looping, set-based operations. If you're not going to change it to a set-based processes then I suggest you not bother. How is the output consumed?

Be One with the Optimizer
TG
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/24/2009 :  16:29:19  Show Profile  Reply with Quote
In general, there is no reason to think that a while loop would be faster than a cursor, especially since cursor processing usually includes a while loop.

Most problems can be solved with set-based queries, instead of cursors or while loops. If you want any help with that, you would have to supply specifics of what you are trying to do.





CODO ERGO SUM
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 08/24/2009 :  16:30:17  Show Profile  Reply with Quote
A cursor and a loop are normally bad practicies due to there is almost always a better way to query the data that is more efficiant.

However in the scenerio you described you mentioned you need one row at a time returned, which kind of forces you to use one of the loop methods. I guess the first question is why do you want to pull 1 record at a time vs's pulling all the records at once with the information how you listed?

If there is a reason you want to proceed using the 1 record at a time then the basics behind a while loop is fairly simply.


Declare @MyCount int
set @MyCount = 0

Select Row_Number() over (order by col1) as RowID,a.*
into #MyTmpData
from MyTable a

while exists (Select * from #MyTmpData a where a.RowID > @MyCount)
BEGIN
  SET @MyCount = @MyCount + 1
  Select .... where RowID = @MyCount
END


It's likley in this scenerio, a cursor would out perform a while loop due to it is querying the resultset fewer times.


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

IncisiveOne
Starting Member

Australia
36 Posts

Posted - 08/25/2009 :  08:44:02  Show Profile  Reply with Quote
1. I agree, set-processing is light years ahead of row-by-row-processing
2. But if you must revert to 1960's style, then a while loop is more efficient than a cursor. Try it for yourself on a few thousand rows in production; they use different locks and more overhead. Additionally, there are issues with the underlying data (the actual data rows in the table) changing whilst the cursor is active. All tha can be easily avoided with a simple while loop (which remains stupid and slow compared to set-oriented processing).
3. I do not understand the need for Row_Number/row_id. Here's the simplest while loop (it can be even more terse, but I have provided it in simple form so as to be easily understood and changed):
DECLARE @MyKey int -- or char or whatever
SET ROWCOUNT 1
SET @MyKey = 0

SELECT @MyKey = MyKey, ...
    FROM MyTable
    WHERE MyKey > @MyKey
WHILE (@@ROWCOUNT != 0)
    BEGIN
    -- processing goes here
    -- set it up for the next iter
    SELECT @MyKey = MyKey, ...
        FROM MyTable
        WHERE MyKey > @MyKey
    END
SET ROWCOUNT 0
Here's another:
DECLARE @MyKey CHAR(12)
SET ROWCOUNT 1
SET @MyKey = 0

WHILE (1=1)
    BEGIN
    SELECT @MyKey = MyKey, ...
        FROM MyTable
        WHERE MyKey > @MyKey
    IF @@ROWCOUNT = 0
        BREAK
    -- processing goes here
    END
SET ROWCOUNT 0


Compound keys, etc are quite easy; post if you need the code.

Cheers
Anything worth doing, is worth doing right.

Edited by - IncisiveOne on 08/25/2009 08:49:31
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/25/2009 :  09:53:02  Show Profile  Reply with Quote
quote:
Originally posted by IncisiveOne

2. But if you must revert to 1960's style, then a while loop is more efficient than a cursor. Try it for yourself on a few thousand rows in production; they use different locks and more overhead. Additionally, there are issues with the underlying data (the actual data rows in the table) changing whilst the cursor is active. All tha can be easily avoided with a simple while loop (which remains stupid and slow compared to set-oriented processing).



There may be cases where a while loop with selects is more efficient, but your statement is way too general to accept as a general statement. For example, if the individual selects cannot use an index, a while loop would result in a series of table scans, while a cursor would only have to scan the table once.

The kind of locks depend on the type of cursor that you declare. If you declare an insensitive cursor, all data is fetched from a temporary table in tempdb.

As for the data changing while you are executing the loop, again that depends on the type of cursor. In the while loop, it also depends on the type of transaction isolation you are using if you are running in a transaction.





CODO ERGO SUM
Go to Top of Page

IncisiveOne
Starting Member

Australia
36 Posts

Posted - 08/25/2009 :  10:24:55  Show Profile  Reply with Quote
quote:
There may be cases where a while loop with selects is more efficient, but your statement is way too general to accept as a general statement.

Fine. Take the example here. A cursor to do:
SELECT @MyKey = MyKey, ...
FROM MyTable
WHERE MyKey > @MyKey
is more expensive than the select. Period.

Anything worth doing, is worth doing right.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/25/2009 :  11:18:48  Show Profile  Reply with Quote
quote:
Originally posted by IncisiveOne

quote:
There may be cases where a while loop with selects is more efficient, but your statement is way too general to accept as a general statement.

Fine. Take the example here. A cursor to do:
SELECT @MyKey = MyKey, ...
FROM MyTable
WHERE MyKey > @MyKey
is more expensive than the select. Period.

Anything worth doing, is worth doing right.



It would be helpful if you posted code that would actually work. Your select would probably only go through the loop one time, because it would scan the table in key sequence, and end up with the values from the last row.

Actually your statement is a good illustration of the problems with using a while loop with select: making sure that you actually process the data in order.




CODO ERGO SUM
Go to Top of Page

IncisiveOne
Starting Member

Australia
36 Posts

Posted - 08/25/2009 :  20:30:35  Show Profile  Reply with Quote
You keep bringing up new "points" without acknowledging that you have lost the previous one.

What is it now, are you suggesting that, given a Relational database, which has a relational logical key in the table, when one has to loop through the rows (which remains stupid and slow in comparison with set-oriented processing), one should ignore the existing order ? Make an additional temp table with a row_id or Identity column instead, just for the query, every execution (with all the additional resources and overhead) ?

Have you noticed that, as suggested in post 4, repeating (select *...) in every execution of the while loop, in addition to the processing inside the loop, is massively inefficient compared to a while loop that is absent such a (select *) ? Have you noticed that post 5 is put forward as a more efficient method ?

quote:
It would be helpful if you posted code that would actually work.
No doubt. The code "actually works" in production for many years. Why is it that you postulate without actually trying the code, or understanding it ? In post 5, the first is instantly recognisable to people who use cursors, as per OP's question; the second is a tighter construct.
quote:
Your select would probably only go through the loop one time, because it would scan the table in key sequence, and end up with the values from the last row.
Probably ? Read the manual on set rowcount before speculating about what you do not know. The context is OP's requirement to replace a cursor with a while loop; so no, I am not providing something other than that; and no, this is not the first time; and no, I am not posting untried code (perhaps that is what you are used to here).

I will not waste time arguing with such postulations and speculations. I will answer anyone who has actually bothered to try it.

Anything worth doing, is worth doing right.

Edited by - IncisiveOne on 08/25/2009 20:32:00
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/28/2009 :  10:14:20  Show Profile  Reply with Quote
quote:
Originally posted by IncisiveOne

You keep bringing up new "points" without acknowledging that you have lost the previous one.

What is it now, are you suggesting that, given a Relational database, which has a relational logical key in the table, when one has to loop through the rows (which remains stupid and slow in comparison with set-oriented processing), one should ignore the existing order ? Make an additional temp table with a row_id or Identity column instead, just for the query, every execution (with all the additional resources and overhead) ?

Have you noticed that, as suggested in post 4, repeating (select *...) in every execution of the while loop, in addition to the processing inside the loop, is massively inefficient compared to a while loop that is absent such a (select *) ? Have you noticed that post 5 is put forward as a more efficient method ?

quote:
It would be helpful if you posted code that would actually work.
No doubt. The code "actually works" in production for many years. Why is it that you postulate without actually trying the code, or understanding it ? In post 5, the first is instantly recognisable to people who use cursors, as per OP's question; the second is a tighter construct.
quote:
Your select would probably only go through the loop one time, because it would scan the table in key sequence, and end up with the values from the last row.
Probably ? Read the manual on set rowcount before speculating about what you do not know. The context is OP's requirement to replace a cursor with a while loop; so no, I am not providing something other than that; and no, this is not the first time; and no, I am not posting untried code (perhaps that is what you are used to here).

I will not waste time arguing with such postulations and speculations. I will answer anyone who has actually bothered to try it.

Anything worth doing, is worth doing right.


I didn’t notice the SET ROWCOUNT 1 from your original post, I was looking at the code form your second post.

However, that doesn’t make your code any more valid, just more dangerous. Maybe you are not aware that SQL Server makes no promises about the order that rows are returned if you do not have an ORDER BY clause. Since your code does not have an ORDER BY, there is no guarantee is will return the next row in sequence by key. The fact that is will usually do that is meaningless, since it can change at any time.

Of course, the select could be written to use SELECT TOP 1 with an ORDER BY to overcome this.

Testing that I did shows a small advantage for using a SELECT TOP 1 in a while loop over a cursor in the limited scenario of processing the loop in order by an index. Of course, as I already pointed out, the cursor will be much faster in the situation where you are not processing in order by an index. I doubt a while loop will perform well compared to a cursor if the select is a join, especially on a column that is not indexed.

Although the OP did not post the code, the description of the problem indicates that the cursor is not a simple ordered forward on a primary key, so I doubt that a while loop is a better solution than a cursor.




CODO ERGO SUM
Go to Top of Page

IncisiveOne
Starting Member

Australia
36 Posts

Posted - 08/30/2009 :  14:14:30  Show Profile  Reply with Quote
quote:
Maybe you are not aware that SQL Server makes no promises about the order that rows are returned if you do not have an ORDER BY clause.

Marvellous. Since you understand the issue, and since you are a coder, and since the technique has to do with walking down a sequence of keys, you should know that. Much ado about nothing. Feel free to add an ORDER BY where it is necessary, and not where it is not (do read up on clustered index). Feel free to write nested loops for compound keys and a single loop for single columns keys and IDENTITY columns. Feel free to read and understand the code in the context of the original post, or post nonsense about something else. Hey, it's the SQL Team site !

"Dangerous". Sorry, I did not realise it needed to be said: Any code is dangerous when you do not know the table/indices or what the particular code segemnt does or you haven't a clue about programming or you code for single-user and port the code to production or you think normalisation is dumbing down the Einsteins of the world so that duh Homer Simpsons can understanduh it. But you are welcome to follow me around, and post all the warnings following my posts, to ensure that the Homers do not trip over themselves. I am new to the site, you will have a much better idea re the technical ability of posters, and thus the full extent of warnings and gotchas required. You should check out some of the other threads here, some a very dangerous.

My warning: the code is only as valid as the coder. MVJ: noting the entire thread, and how long it took you to figure out each of the bits, this code is strictly off-limits for you, do not use it under any circumstances. I am not saying you are dishonest and counter-productive, no, I am saying that it is dangerous for you to use this code. This code is only useful (safe) for people who have prior experience with the techniques, who are capable of using a new method, and who can understand it.

TOP 1. That uses a worktable. But I suppose anything is better than conceding IncisiveOne a point. Maybe you can find a way of using a materialised view plus the original cursor that OP wants to replace. Or maybe a select * in every ieration plus a fabricated row_id. Winnie has an additional temp table, maybe you can find a way to use two temp tables. How about an outer join.


Anything worth doing, is worth doing right.
But if you are MVJ, it's worth doing it wrong a few times first.

Edited by - IncisiveOne on 08/30/2009 14:26:03
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/30/2009 :  14:40:23  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by IncisiveOne Feel free to add an ORDER BY where it is necessary, and not where it is not (do read up on clustered index).


Clustered indexes do not, and never have, guaranteed the order of data returned. Yes, SQL Server will usually return data in the order of the index that was used, but that's in the absence of hash operators, and it is not 100% guaranteed to always happen under all circumstances.

In fact, it's fairly trivial to produce a query that scans a cluster and does not return rows in the order of the cluster (just involves changing the isolation level from the default one)

I can refer you to a blog post written by one of the SQL dev team if you want.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/31/2009 :  17:22:05  Show Profile  Reply with Quote
quote:
Originally posted by IncisiveOne

quote:
Maybe you are not aware that SQL Server makes no promises about the order that rows are returned if you do not have an ORDER BY clause.

Marvellous. Since you understand the issue, and since you are a coder, and since the technique has to do with walking down a sequence of keys, you should know that. Much ado about nothing. Feel free to add an ORDER BY where it is necessary, and not where it is not (do read up on clustered index). Feel free to write nested loops for compound keys and a single loop for single columns keys and IDENTITY columns. Feel free to read and understand the code in the context of the original post, or post nonsense about something else. Hey, it's the SQL Team site !

"Dangerous". Sorry, I did not realise it needed to be said: Any code is dangerous when you do not know the table/indices or what the particular code segemnt does or you haven't a clue about programming or you code for single-user and port the code to production or you think normalisation is dumbing down the Einsteins of the world so that duh Homer Simpsons can understanduh it. But you are welcome to follow me around, and post all the warnings following my posts, to ensure that the Homers do not trip over themselves. I am new to the site, you will have a much better idea re the technical ability of posters, and thus the full extent of warnings and gotchas required. You should check out some of the other threads here, some a very dangerous.

My warning: the code is only as valid as the coder. MVJ: noting the entire thread, and how long it took you to figure out each of the bits, this code is strictly off-limits for you, do not use it under any circumstances. I am not saying you are dishonest and counter-productive, no, I am saying that it is dangerous for you to use this code. This code is only useful (safe) for people who have prior experience with the techniques, who are capable of using a new method, and who can understand it.

TOP 1. That uses a worktable. But I suppose anything is better than conceding IncisiveOne a point. Maybe you can find a way of using a materialised view plus the original cursor that OP wants to replace. Or maybe a select * in every ieration plus a fabricated row_id. Winnie has an additional temp table, maybe you can find a way to use two temp tables. How about an outer join.


Anything worth doing, is worth doing right.
But if you are MVJ, it's worth doing it wrong a few times first.



As I said before (as well as Gail) a SELECT without an ORDER BY does not guarantee any order, regardless of the existence of clustered or non-clustered indexes.

As for temp tables with TOP, when I compared loops using SELECT TOP with an ORDER BY to your code, the performance was identical for each on three different systems running SQL 2000, 2005, and 2008.

Since the SELECT TOP with an ORDER BY does not contain the bug that you code does, why take a chance on yours when there is a working alternative?

While loop using SELECT TOP with an ORDER BY:

declare @MyKey int
select @MyKey = -9999999

while (1=1)
	begin

	select	top 1
		@MyKey = MyKey
	from
		MyTable
        where
		MyKey > @MyKey
	order by
		MyKey

	if @@rowcount = 0
		break
	
	end






CODO ERGO SUM
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 12/25/2010 :  20:01:51  Show Profile  Reply with Quote
quote:
Originally posted by vmessy

Hi all,

Im trying to write a query which will output a row at a time.


@vmessy,

I have to ask... why do you need an output one row at a time? And, yes... it's important. There may be a way around it.

Edited by - Jeff Moden on 12/25/2010 20:06:08
Go to Top of Page

quibbler
Starting Member

1 Posts

Posted - 09/08/2011 :  18:55:50  Show Profile  Reply with Quote
It's not my intent to tar all responders with one brush, but on these forums I see an astonishing level of snobbery, condescension, and insults--in rare cases by people who purportedly work for Microsoft. If you want to make the point that set-based solutions are better than cursor solutions (or any other point), why not just state it plainly? Playing some kind of ego game with newbies doesn't help anyone.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/09/2011 :  02:04:41  Show Profile  Reply with Quote
quote:
Originally posted by quibbler

It's not my intent to tar all responders with one brush, but on these forums I see an astonishing level of snobbery, condescension, and insults--in rare cases by people who purportedly work for Microsoft. If you want to make the point that set-based solutions are better than cursor solutions (or any other point), why not just state it plainly? Playing some kind of ego game with newbies doesn't help anyone.



I don't see where anyone was nasty with the original poster.

There was certainly a bit of rough back and forth with IncisiveOne, but he was hardly a newbie (he claimed to be a senior level guru), and he certainly handed out more than his share of bad feelings. There were other threads where he really went way off the rails and made this thread seem tame. He quit posting here when he realized that people were not going to just accept his BS without question.

Not sure why you feel bringing this old thread to comment on was so important for your first post on SQLTeam, especially since it doesn't seem to apply to the point you are making.





CODO ERGO SUM

Edited by - Michael Valentine Jones on 09/09/2011 02:11:55
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000