April 14, 2009 at 11:27 am
Jerome Grimmer (4/14/2009)
Since cursors are so "evil", I would like to see someone write sql that will send emails to a table of users. Each user should receive a different email containing only data that they are responsible for updating in the database.Having said that, this is nearly the only thing that we use cursors for, sending email to users in the field, where each user receives a different email because they are responsible for a different subset of the data in the database. I've not yet found a way to do this without using cursors or while loops.
Hmm, this is a more interesting Email case. Obviously aggregated execution would work, but I always like to look for a better solution. Notification Services mightwork for this, but it might also be overkill. I'll have to think on this one... 🙂
[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]
April 14, 2009 at 11:29 am
Thanks that worked
Heather
April 14, 2009 at 11:36 am
ManyHats11 (4/14/2009)
I thoroughly enjoyed reading this. I do use cursors when I get stuck and I'm anxious to see if I am able to pick up any new ticks.
Thanks, I think that you're probably my ideal audience. 🙂
[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]
April 14, 2009 at 11:40 am
bruce.trimpop (4/14/2009)
Unfortunately, the "readily consumable format" is a problem. Packaging something up that would cover all or most of the critical functional variations would be a bit time consuming. Thanks though.
My point is just that I agree that using a cursor just because it's the easy solution is a bad approach, that doesn't mean that cursors should never be used. I've found that many "older" developers that came from a row based procedural style of data processing (xBase for example...gosh did I just date myself :-D) found that cursors fit their logic model more comfortably that set based logic. As such I've run into quite a bit of SQL code that used cursors when set based solutions are obviously the better choice. Those fall under the "cursor bad!" category. I've also run into situations where using a cursor is a proper fit, just one more tool in the tool box as it were.
That's a shame - sounded like it could have been an excellent demonstration because of the complexities involved. Using things like windowed functions would probably have given a really good example of how SS2K5 has helped in the eradication of cursors.
If you do feel inclined to post a bit of DDL/DML to set up a test, would be *sooo* appropriate right here 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 14, 2009 at 11:48 am
Lynn Pettis (4/14/2009)
I know I'm late adding this Barry, but I have to admit that I was right. During The Rant, Steve sent me a PM asking me to write an article like this. You have done an awesome job starting this, and I can't wait to see the what else you have written on this subject. Please feel free to bounce things off me as well if you need another set of eyes.
Thanks Lynn. 🙂
[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]
April 14, 2009 at 11:57 am
Matt Whitfield (4/14/2009)
bruce.trimpop (4/14/2009)
Unfortunately, the "readily consumable format" is a problem. Packaging something up that would cover all or most of the critical functional variations would be a bit time consuming. Thanks though.
My point is just that I agree that using a cursor just because it's the easy solution is a bad approach, that doesn't mean that cursors should never be used. I've found that many "older" developers that came from a row based procedural style of data processing (xBase for example...gosh did I just date myself :-D) found that cursors fit their logic model more comfortably that set based logic. As such I've run into quite a bit of SQL code that used cursors when set based solutions are obviously the better choice. Those fall under the "cursor bad!" category. I've also run into situations where using a cursor is a proper fit, just one more tool in the tool box as it were.
That's a shame - sounded like it could have been an excellent demonstration because of the complexities involved. Using things like windowed functions would probably have given a really good example of how SS2K5 has helped in the eradication of cursors.
If you do feel inclined to post a bit of DDL/DML to set up a test, would be *sooo* appropriate right here 🙂
I'll see if I can come up with something stripped down, but meaningful. I'd love to see some alternate (non-cursor) solutions for this 🙂 I admit I have not revisited this in SS2K5 since it still needs to work in SS2K and it's not a high priority. It may take a day or two to come up with something if folks can wait that long!
April 14, 2009 at 11:58 am
RBarryYoung (4/14/2009)
neufeldb4 (4/13/2009)
Allow me to make some observations....
This is the type of problem I have seen in 100% of the articles like yours that claim the cursor is a thing to be scorned from the archaic past - none of them show a properly built cursor. This definitely impacts my perception as to the credibility and ability of the author. If you make mistakes that I do not tolerate in junior programmers, then how am I to take anything you say subsequently as being worth serious consideration? I do make use of cursors for some of my processes and, I can assure you, that the instances where I use them, one would not be able to improve either performance or readability with their removal.
Well, you have me there. My expertise is in re-writing Cursor routines, not in writing them in the first place. If you could please demonstrate for us an example of this as a "properly built cursor" then I will endeavor to use such in my examples and comparisons. It is neither my intent nor my desire to use inferior versions of the Cursor examples to make my points.
OK, I went back and re-read my article and I think I know what you are getinng at here. and yes, there are two mistakes in the example: First, I left off the keyword "STATIC" on the Cursor options and secondly the last two lines (CLOSE, and DEALLOCATE) were lost when I cut and pasted it. Here is the corrected example:Declare @dummy int
Declare @X int
Set @X = 0
Declare XCursor Cursor STATIC LOCAL FORWARD_ONLY READ_ONLY For
Select 1
From master.sys.columns c1
Cross Join master.sys.columns c2
OPEN XCursor
FETCH NEXT FROM XCursor Into @Dummy
WHILE @@Fetch_Status = 0
Begin
Set @X = @X + 1
FETCH NEXT FROM XCursor Into @Dummy
End
Print @X
Close XCursor
Deallocate XCursor
I apologize for the typos, I did proofread it several times, but those two mistakes still got through. Sorry.
[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]
April 14, 2009 at 11:59 am
bruce.trimpop (4/14/2009)
Matt Whitfield (4/14/2009)
bruce.trimpop (4/14/2009)
Unfortunately, the "readily consumable format" is a problem. Packaging something up that would cover all or most of the critical functional variations would be a bit time consuming. Thanks though.
My point is just that I agree that using a cursor just because it's the easy solution is a bad approach, that doesn't mean that cursors should never be used. I've found that many "older" developers that came from a row based procedural style of data processing (xBase for example...gosh did I just date myself :-D) found that cursors fit their logic model more comfortably that set based logic. As such I've run into quite a bit of SQL code that used cursors when set based solutions are obviously the better choice. Those fall under the "cursor bad!" category. I've also run into situations where using a cursor is a proper fit, just one more tool in the tool box as it were.
That's a shame - sounded like it could have been an excellent demonstration because of the complexities involved. Using things like windowed functions would probably have given a really good example of how SS2K5 has helped in the eradication of cursors.
If you do feel inclined to post a bit of DDL/DML to set up a test, would be *sooo* appropriate right here 🙂
I'll see if I can come up with something stripped down, but meaningful. I'd love to see some alternate (non-cursor) solutions for this 🙂 I admit I have not revisited this in SS2K5 since it still needs to work in SS2K and it's not a high priority. It may take a day or two to come up with something if folks can wait that long!
I don't think that will be a problem. Once you do, it also gives us time to work on it without a lot of pressure as well.
April 14, 2009 at 12:02 pm
Not that another vote of confidence is needed, but I'm going to add one anyway. Good intro to the subject, Barry. Certainly a lot less dry than many I've seen (excepting, of course, everything ever written by Phil Phactor).
One of the things I've noticed about people who claim that they have something "far too complex to ever be done without cursors", is that they rarely have the courage to actually post the problem. I've seen thousands of cursors, and I've never yet run into one that couldn't be replaced with something much, much faster.
That wasn't totally true in 2000, but has been so far in 2005/2008.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2009 at 12:17 pm
RBarryYoung (4/14/2009)
RBarryYoung (4/14/2009)
neufeldb4 (4/13/2009)
Allow me to make some observations....
This is the type of problem I have seen in 100% of the articles like yours that claim the cursor is a thing to be scorned from the archaic past - none of them show a properly built cursor. This definitely impacts my perception as to the credibility and ability of the author. If you make mistakes that I do not tolerate in junior programmers, then how am I to take anything you say subsequently as being worth serious consideration? I do make use of cursors for some of my processes and, I can assure you, that the instances where I use them, one would not be able to improve either performance or readability with their removal.
Well, you have me there. My expertise is in re-writing Cursor routines, not in writing them in the first place. If you could please demonstrate for us an example of this as a "properly built cursor" then I will endeavor to use such in my examples and comparisons. It is neither my intent nor my desire to use inferior versions of the Cursor examples to make my points.
OK, I went back and re-read my article and I think I know what you are getinng at here. and yes, there are two mistakes in the example: First, I left off the keyword "STATIC" on the Cursor options and secondly the last two lines (CLOSE, and DEALLOCATE) were lost when I cut and pasted it. Here is the corrected example:
Declare @dummy intDeclare @X int
Set @X = 0
Declare XCursor Cursor STATIC LOCAL FORWARD_ONLY READ_ONLY For
Select 1
From master.sys.columns c1
Cross Join master.sys.columns c2
OPEN XCursor
FETCH NEXT FROM XCursor Into @Dummy
WHILE @@Fetch_Status = 0
Begin
Set @X = @X + 1
FETCH NEXT FROM XCursor Into @Dummy
End
Print @X
Close XCursor
Deallocate XCursor
I apologize for the typos, I did proofread it several times, but those two mistakes still got through. Sorry.
No need to apologize here. I can't count the number of times I have done this and have to have someone else point it out to me. The left out code is still inconsequential. Reason being that the query still takes at least in my environment 9 to 10 seconds or so.
BTW, I forgot to mention how much I enjoyed the article and look forward to the next installment. The information contained within, is an education for some of us that are still learning this very intriguing T-SQL language. For that, I thank you!
April 14, 2009 at 12:37 pm
longobardia (4/14/2009)
RBarryYoung (4/14/2009)...
OK, I went back and re-read my article and I think I know what you are getinng at here. and yes, there are two mistakes in the example: First, I left off the keyword "STATIC" on the Cursor options and secondly the last two lines (CLOSE, and DEALLOCATE) were lost when I cut and pasted it. Here is the corrected example:
Declare @dummy intDeclare @X int
Set @X = 0
Declare XCursor Cursor STATIC LOCAL FORWARD_ONLY READ_ONLY For
Select 1
From master.sys.columns c1
Cross Join master.sys.columns c2
OPEN XCursor
FETCH NEXT FROM XCursor Into @Dummy
WHILE @@Fetch_Status = 0
Begin
Set @X = @X + 1
FETCH NEXT FROM XCursor Into @Dummy
End
Print @X
Close XCursor
Deallocate XCursor
I apologize for the typos, I did proofread it several times, but those two mistakes still got through. Sorry.
No need to apologize here. I can't count the number of times I have done this and have to have someone else point it out to me. The left out code is still inconsequential. Reason being that the query still takes at least in my environment 9 to 10 seconds or so.
BTW, I forgot to mention how much I enjoyed the article and look forward to the next installment. The information contained within, is an education for some of us that are still learning this very intriguing T-SQL language. For that, I thank you!
Thanks for the confidence. 🙂
[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]
April 14, 2009 at 12:46 pm
RBarryYoung (4/14/2009)
RJ (4/14/2009)
RBarryYoung,I would like to see how you can eliminate using a CURSOR or WHILE loop for sending emails using "msdb.dbo.sp_send_dbmail". My requirement would be to send email notifications to managers whenever there was an action taken against users (not database or server level users they are custom users created by the software application) specific to the application.
This is a nightly job that pulls all users who satisfy few conditions and users & managers are notified by emails. How do I eliminate a looping functions to send emails?
Thanks
RJ
Great example, RJ. Of course, A specific answer will be dependent on the specifics details of your requirements. For instance is "msdb.dbo.sp_send_dbmail" really a business requirement or is it just assumed to be the means that you will have to use?
General speaking I can see 4 possible ways to approach this that do not use Cursors: distribution lists, NS (Notification Services), SQL CLR (or external activation), and aggregated execution. Distribution lists are usually the preferred solution.
RJ, I hope you don't mind but I'm going to jump in here. Barry, maybe RJ's requirements is small enough to justify a distribution list but every year twice a year I have to send out invoices and statements via e-mail to an estimated 15000 people for a client of mine. Would you put that in a distribution list? How would you actually handle that? I am anxious to read your response. You see, the particular query you did as an example I would never put in a cursor. It is a bit unfair to use an example like that and say cursors are bad. Steve had an article a couple of days ago about breaking down code in a stored proc/query to make it more readable and editable. Sometimes cursors help you to do just that.
Oh, by the way, your query result on my database is 438244 rows and after 16 minutes I stopped the query because it is pulling the performance on my laptop down. I would never put that into a cursor. Did I say that already? Well, I will never put that in a cursor!
:-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)
April 14, 2009 at 1:10 pm
I am also looking forward to part due. I really try not to use cursors, but sometimes get in traps where I can not think of a better solution. anyway I am always looking for geeky stuff like this.
April 14, 2009 at 1:28 pm
GSquared (4/14/2009)
Not that another vote of confidence is needed, but I'm going to add one anyway. Good intro to the subject, Barry. Certainly a lot less dry than many I've seen (excepting, of course, everything ever written by Phil Phactor).....
Thanks, Gus. And yes, I am definitely not worthy to purple Phil's prose. 😀
[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]
April 14, 2009 at 1:33 pm
Manie Verster (4/14/2009)
...RJ, I hope you don't mind but I'm going to jump in here. Barry, maybe RJ's requirements is small enough to justify a distribution list but every year twice a year I have to send out invoices and statements via e-mail to an estimated 15000 people for a client of mine. Would you put that in a distribution list? How would you actually handle that? I am anxious to read your response. You see, the particular query you did as an example I would never put in a cursor. It is a bit unfair to use an example like that and say cursors are bad. Steve had an article a couple of days ago about breaking down code in a stored proc/query to make it more readable and editable. Sometimes cursors help you to do just that...
Check out Notification Services. I am no expert in it, but I have heard that it is designed to send out millions of customized emails at the same time.
[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 - 91 through 105 (of 380 total)
You must be logged in to reply to this topic. Login to reply