May 1, 2009 at 1:45 am
I haven't taken a shot at this new problem yet, waiting for replies to the "gserdijn" solution first :). I did take a look at the problem code however and it took me like 10 minutes to see exactly what was going on. And yet I program every day procedurally in other languages! Personally I can say it is very hard to follow and has some counter intuitive 'moves', to name a few:
* Counting down, suggesting you are examining the rightmost character every time.
* Modifying the original string using a RIGHT function to make it one character shorter, its like trying to keep track of a moving target. You did this in order to be able to the LEFT function to isolate one character on every iteration. At first glance I was stunned to see an assignment and modifying of string happen...and was WHY...we just needed to look at every character right?
This suggest to me you did not look in Books online to see what equivalent function there is for MID. The one you needed here to make the code better understandable is called SUBSTRING, and when coding around that function it is instantly clear that your code is inspecting characters at various positions according to some counter. I had to figure that out in your code despite the comments you made in your post!
So lets assume you were unfamiliar with the SUBSTRING function, then given that you know LEFT and RIGHT, it would be better readable if you examined the right most character every time and use LEFT to make the string shorter, so that all characters that still need to be processed stay in the their original position. And the counter would then move in the same direction as the positions of the characters you examine.
Other then that, your code is pretty easy replaceable with a set based solution, and since I have a day off (but am still reading), I rather wait for your response to the solution already offered. Instead of going on a coding spree myself 🙂 One comment on his solution tho, I haven't run it, but I think it might return too many records for strings that are smaller then the maximum occurring length. It all depends on ASCII returning NULL on empty strings or not, and I do not know this from the top of my head!
May 1, 2009 at 6:53 am
Bruce W Cassidy (4/30/2009)
[font="Verdana"]This is another "Look Ma! No cursor!" approach. Of the two, I would rather take the cursor (actually, just a loop, no cursor). Then I can easily include error handling and so on.[/font]
I don't know why you think I can't include all the error handling I want in the create a string of commands and use exec approach - it's absolutely trivial to do so. And since we are talking about an email stored proc, there are no any options without a loop unless we take it outside of SQL - but does your "Look Ma, No CUrsor" comment not apply equally to your "actually, just a loop, no cursor"? (are you trying to be offensive with that comment? - I guess you probably are, and you've failed because I'm not at all offended by your demonstration that you are rather silly) and does it mean that you think all loops are essentially the same as cursors? The difference sometimes (as in this case) boils down to which is the easiest to understand - and I find just about anything easier to understand than cursors. We certainly aren't concerned about performance here, are we?
[font="Verdana"]Why do I say that? All you are doing with this approach is serializing the procedure calls with dynamic SQL (as distinct from serializing the procedure calls with a loop over a cursor). So you end up with a whole string of procedure calls, just as you do with a loop. No effective difference to the cursor, just a change in syntax.[/font]
Well, please explain why your "actually, just a loop, no cursor" is any different in this respect - I don't believe it is. Not the slightest bit different in that respect.
[font="Verdana"]So now I am looking forward to Barry's solution! On the other hand, comments were made against the first article about getting operations like mailouts out of the database, with which I agree. So SQL Server Notification Services/Reporting Services could be a better way to do this.
[/font]
Another approach would be to write an extended stored proc that uses (for example) CDO to send mail; then create a UDF that calls the extended stored proc, and use cross apply. That of course would not be productive use of one's time - I don't think it would buy any performance (well, given the quality of some of MS's stored procs the new extended stored proc might be a bit faster than the usual sp for sending mail) or any parallelism in practice: T-SQL needs a proper MAP operator for this sort of thing (to make code easier to read) and it needs significant changes to teh optimiser to be able to use MAP to get performance improvements.
Tom
May 1, 2009 at 7:01 am
Cross Apply usually results in hidden RBAR so no real benefit over an efficient cursor based procedure
for a simple T-SQL mailing proc the simplest solution is
Insert mailing data into temp table (or another table built to be used exclusively by the procedure), this part is set based and flattens the data ready for processing
Open a cursor on to the temp table and process each row through the dbmail procedure
Close the cursor and truncate the temp table
alternatively it could be done with a while loop
May 1, 2009 at 9:43 am
In my opinion sending mail from within SQL server is only sensible for maintenance reports for DBA's and the occasional alert if something goes wrong.
I sincerely doubt it was ever meant for bulk mailing, the idea alone sounds out of place to me. A client application that fetches 200-1000 mails per run in batch and then sends those using a standard mail API is a much sounder design. In SQL you want transactions to be as non-blocking and short living as you possibly can, mail just doesn't fit that bill. This is because trough the API you interact with another service with has its own set of domain specific issues! There is no need to have those two services have such intimate contact.
Besides no one here will claim SQL Sever is the best tool for every job. Sending mail is like using a hammer to get a screw in IMHO. Making the quest for cursor less in this case also a non-issue, you shouldn't have the problem in the first place.
May 3, 2009 at 3:36 pm
Tom.Thomson (5/1/2009)
Bruce W Cassidy (4/30/2009)
[font="Verdana"]This is another "Look Ma! No cursor!" approach. Of the two, I would rather take the cursor (actually, just a loop, no cursor). Then I can easily include error handling and so on.[/font]I don't know why you think I can't include all the error handling I want in the create a string of commands and use exec approach - it's absolutely trivial to do so. And since we are talking about an email stored proc, there are no any options without a loop unless we take it outside of SQL - but does your "Look Ma, No CUrsor" comment not apply equally to your "actually, just a loop, no cursor"? (are you trying to be offensive with that comment? - I guess you probably are, and you've failed because I'm not at all offended by your demonstration that you are rather silly) and does it mean that you think all loops are essentially the same as cursors? The difference sometimes (as in this case) boils down to which is the easiest to understand - and I find just about anything easier to understand than cursors. We certainly aren't concerned about performance here, are we?
[font="Verdana"]No offense was intended. I made a post earlier showing the difference in using just a plain loop with an embedded select statement versus a cursor. The syntax is much easier than the rather arcane cursor syntax. So it does eliminate the cursor, but it does not eliminate the row-by-row nature of the solution. I appended the code I had posted with "Look Ma, no cursor!" as a way of indicating I was doing something silly.
The serious part of that post was to argue that cursors are not easier to maintain and understand.
For example, a cursor might appear in the form (pseudocode):
declare cursor
open cursor
read from cursor
while a valid row was read from the cursor
process the row
read from cursor again
end while
close cursor
deallocate cursor
An alternative syntax which I find much easier to understand is:
set a starting counter
loop
read the next one row that is past the counter and advance the counter
if no row was read, exit the loop
process the row
end loop
So that was the intention of my post to argue that even the syntax of cursors is not easy to understand.[/font]
Tom.Thomson (5/1/2009)
Well, please explain why your "actually, just a loop, no cursor" is any different in this respect - I don't believe it is. Not the slightest bit different in that respect.
[font="Verdana"]In terms of your post, you were saying to take this:
declare cursor
open cursor
read from cursor
while a valid row was read from the cursor
process the row (execute a stored procedure)
read from cursor again
end while
close cursor
deallocate cursor
With this:
read a list of stored procedure commands into a string
execute the string (
execute a stored procedure
execute a stored procedure
execute a stored procedure...
)
That's a perfectly valid solution, provided you can handle any errors correctly (in my experience, it can get a little convoluted tracking exactly which row generated an error, which is why I would find a looped solution easier to maintain.)
The nature of the solution hasn't changed. It's still executing stored procedures one at a time. Yes, you have avoided using a cursor (congratulations). No, it is still row by row. The overhead of the loop is minimal in comparison to the overhead of calling a stored procedure one row at a time.
[/font]
May 3, 2009 at 3:46 pm
First, my heartfelt thanks to Lynn, Jeff, Jack, Bruce and everyone else who pitched in to cover for me while I was in the hospital for the last week. I especially want to thank Peter who was willing to let bygones go and really did an exceptional job answering questions and providing solutions. You're a real professional, Peter.
I had intended to devote today to reading through every post and trying to answer all unanswered questions. Well, I have done the first part and I must confess that there were so many question and answers going back and forth that I could easily tell who had been answered and who had not.
So I make this offer/request instead: Anyone reading this who still has an unanswered question (or a new question) please reply here to bring it to my attention and I will do my best to get an answer to you.
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 3, 2009 at 4:27 pm
Samuel Vella (5/1/2009)
Cross Apply usually results in hidden RBAR so no real benefit over an efficient cursor based procedure
Heh... as you said, "usually". We've recently done some experiments on some of the Tally table methods. Very much like a CTE behaves, so do some Cross Apply's and sometimes with huge performance gains. I'm not saying that everyone should suddenly go nut's with Cross Apply's any more than they should with CTE's. I'm just saying that there are some great exceptions when "usually" is not the case.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2009 at 7:27 am
gserdijn (5/1/2009)
Manie Verster (4/30/2009) Here is a cursor that I would like you to convert to set for me please. If you can do this I will accept the fact that since SQL Server 2005 cursors are no longer necessary.
Does this work?
DECLARE @MaxLen INT
SELECT @MaxLen = MAX(LEN(firstname + ' ' + lastname )) FROM AdventureWorks.Sales.Customer
SET ROWCOUNT @MaxLen;
SELECT IDENTITY(int,1,1) AS id INTO #Tally FROM sysobjects a;
SET ROWCOUNT 0;
SELECT
SUBSTRING(firstname + ' ' + lastname , id,1) search,
ASCII(SUBSTRING(firstname + ' ' + lastname , id,1)),
firstname + ' ' + lastname [searchstr] FROM AdventureWorks.Sales.Customer CROSS JOIN #Tally tally
WHERE NOT ASCII(SUBSTRING(firstname + ' ' + lastname , id,1)) IS NULL
ORDER BY searchstr, id
DROP TABLE #Tally;
(Luckily you didn't mention performance)
Friend, you taught me something today. With a few minor changes in your query I ran it and got the results that I wanted. Your query returned duplicates so I added DISTINCT and fixed the ORDER BY to accommodate the DISTINCT. I also used Barry's code from his article to check the performance and it's not to shabby.
cpumslogrdselapsed
1562343548
Thanks for the lesson. Barry, get this man to help you!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
May 4, 2009 at 7:33 am
peter (5/1/2009)
I haven't taken a shot at this new problem yet, waiting for replies to the "gserdijn" solution first :). I did take a look at the problem code however and it took me like 10 minutes to see exactly what was going on. And yet I program every day procedurally in other languages! Personally I can say it is very hard to follow and has some counter intuitive 'moves', to name a few:* Counting down, suggesting you are examining the rightmost character every time.
* Modifying the original string using a RIGHT function to make it one character shorter, its like trying to keep track of a moving target. You did this in order to be able to the LEFT function to isolate one character on every iteration. At first glance I was stunned to see an assignment and modifying of string happen...and was WHY...we just needed to look at every character right?
This suggest to me you did not look in Books online to see what equivalent function there is for MID. The one you needed here to make the code better understandable is called SUBSTRING, and when coding around that function it is instantly clear that your code is inspecting characters at various positions according to some counter. I had to figure that out in your code despite the comments you made in your post!
So lets assume you were unfamiliar with the SUBSTRING function, then given that you know LEFT and RIGHT, it would be better readable if you examined the right most character every time and use LEFT to make the string shorter, so that all characters that still need to be processed stay in the their original position. And the counter would then move in the same direction as the positions of the characters you examine.
Other then that, your code is pretty easy replaceable with a set based solution, and since I have a day off (but am still reading), I rather wait for your response to the solution already offered. Instead of going on a coding spree myself 🙂 One comment on his solution tho, I haven't run it, but I think it might return too many records for strings that are smaller then the maximum occurring length. It all depends on ASCII returning NULL on empty strings or not, and I do not know this from the top of my head!
Peter, thaks for your post and after gserdijn's post and yours I see the errors of my ways. gserdijn gave me a great solution and I already thanked him as well.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
May 4, 2009 at 9:16 am
Bruce W Cassidy (4/30/2009)
Tom.Thomson (4/30/2009)
Samuel Vella (4/27/2009)
Jeff Moden (4/27/2009)
Heh... in SQL Server 2005, even that can be done without a cursor. We're just gonna have to wait for it in Barry's series of articles.
OK it *can* be done without a cursor... I have seen some dirty work arounds to get stored procedure calls inside select statements using linked servers but they need certain security features turned on and would still end up as a row by row operation when processed within the database engine (not to mention all the other overheads associated with opening new DB connections) so no performance benefit.
IMO a last resort technique (even after the possibility of SSIS has been excluded).
Why on earth would you need linked servers to do that? It' can be done simply by accumulating a string of commands (perhaps needing execute as) and then calling exec! Of course on SQL 2000 you may run into the infamouts 8000 limit, but this series is explicitly about sql 2005 and onwars.
[font="Verdana"]This is another "Look Ma! No cursor!" approach. Of the two, I would rather take the cursor (actually, just a loop, no cursor). Then I can easily include error handling and so on.
Why do I say that? All you are doing with this approach is serializing the procedure calls with dynamic SQL (as distinct from serializing the procedure calls with a loop over a cursor). So you end up with a whole string of procedure calls, just as you do with a loop. No effective difference to the cursor, just a change in syntax.
So now I am looking forward to Barry's solution! On the other hand, comments were made against the first article about getting operations like mailouts out of the database, with which I agree. So SQL Server Notification Services/Reporting Services could be a better way to do this.
[/font]
I have noticed in the discussions to this article and my previous one some jockeying and pre-conditioning with respect to the anticipated attempt by me to solve the "Execute a sProc for every row" problem without cursors. I would like to get a clearer understanding of what your understanding of an acceptable solution really is.
Therefore, I would like to ask Bruce and Samuel if they could describe what a minimally acceptable non-cursor solution would have to be. I am not asking you to tell me a solution, just what such a solution should look like: what would its characteristics or attributes have to be? By minimally acceptable, I mean, what features would it have to have for you to agree to use it instead of Cursors (or While loops)?
Understand, I am not necessarily committing to these characteristics (after all, I haven't seen them yet), nor am I saying that I have magical solution in my pocket, I just want to get an idea of what folks think the solution should be like.
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 4, 2009 at 11:24 am
Heh... on the other hand, if the worst cursor someone writes is to pass individual records to an email routine, then there also wouldn't be a need for this series of articles. But, that's not normally the case. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2009 at 12:49 pm
Manie Verster (5/4/2009) Friend, you taught me something today.
Thank you for the compliment, Manie. I appreciate it a lot.
😎
May 4, 2009 at 2:37 pm
Manie Verster (5/4/2009)
gserdijn (5/1/2009)
Manie Verster (4/30/2009) Here is a cursor that I would like you to convert to set for me please. If you can do this I will accept the fact that since SQL Server 2005 cursors are no longer necessary.
Does this work?
DECLARE @MaxLen INT
SELECT @MaxLen = MAX(LEN(firstname + ' ' + lastname )) FROM AdventureWorks.Sales.Customer
SET ROWCOUNT @MaxLen;
SELECT IDENTITY(int,1,1) AS id INTO #Tally FROM sysobjects a;
SET ROWCOUNT 0;
SELECT
SUBSTRING(firstname + ' ' + lastname , id,1) search,
ASCII(SUBSTRING(firstname + ' ' + lastname , id,1)),
firstname + ' ' + lastname [searchstr] FROM AdventureWorks.Sales.Customer CROSS JOIN #Tally tally
WHERE NOT ASCII(SUBSTRING(firstname + ' ' + lastname , id,1)) IS NULL
ORDER BY searchstr, id
DROP TABLE #Tally;
(Luckily you didn't mention performance)
Friend, you taught me something today. With a few minor changes in your query I ran it and got the results that I wanted. Your query returned duplicates so I added DISTINCT and fixed the ORDER BY to accommodate the DISTINCT. I also used Barry's code from his article to check the performance and it's not to shabby.
cpumslogrdselapsed
1562343548
Thanks for the lesson. Barry, get this man to help you!
Funny.... AdventureWorks.Sales.Customer has no first or last name columns in my Server... and it's a brand new installation with sp3. In other words, the following fails on my box...
DECLARE @MaxLen INT
SELECT @MaxLen = MAX(LEN(firstname + ' ' + lastname )) FROM AdventureWorks.Sales.Customer
You sure that's the correct table?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2009 at 7:07 pm
very good artical about the lower effieiency cursor, but i still a bittle confuse how it work and it seems that it cannot avoid to use the cursor completely.
There is a requirement to process eash row step by step for a datatable and update the specified columns according to different conditions.
i use the database cursor to fetch each row and process it step by step,
It is very hard to avoid it>>....:hehe:
May 4, 2009 at 7:26 pm
Cade.Bu (5/4/2009)
very good artical about the lower effieiency cursor, but i still a bittle confuse how it work and it seems that it cannot avoid to use the cursor completely.There is a requirement to process eash row step by step for a datatable and update the specified columns according to different conditions.
i use the database cursor to fetch each row and process it step by step,
It is very hard to avoid it>>....:hehe:
Could you provide us with a more detailed explanation or specifications? Perhaps show us the code that you are talking about?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 151 through 165 (of 316 total)
You must be logged in to reply to this topic. Login to reply