April 14, 2009 at 10:01 am
rot (4/14/2009)
Good introduction! Looking forward to reading part 2 (+1).And totally agree with Kev. I only want to add that nobody is asking anybody to fire their cursors if they prefer to keep them ...
And to avoid maybe unnecessary posts ... yes the cursor should be closed and deallocated and select count(*) is not the solution to avoid cursors in the example, although it would suit the requirements here.
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]
April 14, 2009 at 10:03 am
vinizio (4/14/2009)
I think Brad took it personal!!! This article is an opinion and it is up to us to discuss and bring our comments in how to improve the a way to work with cursors. I believe that sometimes cursors might be useful, but after 30 years of programming in different languages and DB, there is always a better way to do it. For instance I was checking a process that was updating some records on a table, and noticed that the process was taken long time to execute, then I check on the code and found that there was a cursor but the problem was not in the cursor itself but in the way the cursor was used; it was updating the table record by record, so I took the result to a temp table and performed a massive update on the final table. What a surprise a process that it was taken more that 30 min. was reduced to 2-3 minutes and even less. In other hand, sometime programmers are assign to do certain taks and they tend to have "TEMPLATES" in their head and are not capable to think out of the box, that is why if you team lack of programming standards and code QA/QC and/or DBA team that approves the code the result is obvious. Remember we work base on FACTS and Barry have an opinion that we have to be intelligent enough (Do not take it personal) to contribute by providing a solution or giving you opinion that stands against the article. Finally I agreed with gautamsheth200 by doing a "Select count(*) From master.sys.columns C1 CROSS JOIN master.sys.columns C2" you have the same result, but once again you have to analyze the situation and see if you are able to improve the code, by the way from 23 to 1 second by the solution presented.Best regards to all
Vinicio Aizpurua
Systems Analyst/Developer/DBA
Thanks, Vincio!
[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 10:03 am
I am going to enjoy by reading your next set of articles on Avoiding Cursors :)..
From now on, if any body asks me on why we should not use cursors then i can ask them to read your article.
April 14, 2009 at 10:05 am
Good article RBarry Young. I got rid of my cursor by
- Store the result data in table var with rownumber
- create a row index var to loop thru each record
- while there are still record available, Select @Var1 = Col1 from @Table where @rowIdx < colRowNum
April 14, 2009 at 10:05 am
Gaby Abed (4/14/2009)
You're a brave man RBarryYoung. At least the comments here have been kinder than with the article I wrote (link omitted on purpose, still shellshocked, LOL)
Thanks, Gaby. Yeah, I knew that I would need my hard hat and flak jacket for this one. But I do think that the best articles are also the ones that generate the most discussion and exchange of opinions and ideas. 🙂
[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 10:10 am
longobardia (4/14/2009)
...Now, I am no SQL Genius but I was able to formulate the following query based on the example:
select max(k.RowNum)
From (
select ROW_NUMBER() over (order by s.dta desc) as 'RowNum'
from (
Select 1 as Dta
From master.sys.columns c1
Cross Join master.sys.columns c2
) as s
) as k
Please feel free to comment or correct if wrong or wrong approach taken.
Al;-)
Now, that's an ingenious approach, Al! However there is an even simpler solution that some others have already posted. Check them out and see what you think.
[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 10:12 am
Manie Verster (4/14/2009)
...Barry, when are you going to give us your comments on what is being said here?
Patience, Mannie. I've still got a job plus at least 6 hours time difference here. I'm getting to them... :-).
[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 10:16 am
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.
April 14, 2009 at 10:18 am
sushila (4/14/2009)
...It's a tough job being able to communicate and get your point across - simplify it, but not to the point where it looks like you're talking to the village idiot; address all the key points and more importantly make them comprehensible to everyone; RBarry - you did a tremendous job of this and struck just the right balance. For my part, I eagerly await part 2....
Sushila: Wow, thanks! It's areal honor to hear that from one of SSC's elusive legends. 🙂
..As for the instances where you use cursors and no one else would be able to "improve either performance or readability with their removal", I volunteer Jeff Moden (Jeff - hope you don't mind!:-)) - give him a chance to take a look at it and we can all credit you with the intelligence you lay claim to, if Jeff cannot remove them and turbo their pace!
Great idea! I "volunteer" Jeff too! (Why should I do all of the hard work?) 😀
[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 10:20 am
gserdijn (4/14/2009)
select square(count(1)) From master.sys.columns C1
Heh, very nice. I think that you may have "cooked" my example!
[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 10:24 am
RBarryYoung (4/14/2009)
longobardia (4/14/2009)
...Now, I am no SQL Genius but I was able to formulate the following query based on the example:
select max(k.RowNum)
From (
select ROW_NUMBER() over (order by s.dta desc) as 'RowNum'
from (
Select 1 as Dta
From master.sys.columns c1
Cross Join master.sys.columns c2
) as s
) as k
Please feel free to comment or correct if wrong or wrong approach taken.
Al;-)
Now, that's an ingenious approach, Al! However there is an even simpler solution that some others have already posted. Check them out and see what you think.
I know that. I was just following the exact example and providing a similar solution. Sometimes, I like to over board (I admit). I knew that I could use a count(*). However, I never thought of using the Square function for this. You learn something new every day!.:w00t:
April 14, 2009 at 10:31 am
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.
[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 10:41 am
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 user Cursors: distribution lists, NS (Notification Services), SQL CLR (or external activation), and aggregated execution. Distribution lists are usually the preferred solution.
Awesome solution probabilities RBarryYoung, Yes.. you can also make it a SSRS report with a subscription(users). You could also add a SQL job to fire up a stored procedure that would generate the data for the subscribed report and the have an email sent to the subscriber that his report/data is available for use.
I do this all the time. Another alternative to ponder upon.
AL:hehe:
April 14, 2009 at 10:45 am
bruce.trimpop (4/14/2009)
Well I'm no expert, but given the "forward only" nature of the example cursor, seems like it should be a"FAST_FORWARD" cursor to allow for performance optimizations. ...
Yikes! Excellent point, Bruce. I think that you have found a typo in my example, as the keyword "STATIC" should have been on that cursor. According to Hugo Kornelius these are even faster than FAST_FORWARD cursors, but I dropped the "STATIC" somehow.
The correct listing should be:
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
Also, I have to agree that there are times when using a cursor makes sense. I have functionality that relies on user entered tables, columns, and conditionals. The procedure needs to skip through those row by row and build/execute dynamic queries based on those user entered values. While it might be possible to do that without cursors, the complexity of the code makes it virtually unusable/unsupportable/unmaintainable as well as pretty darn slow.
I would love to see an example, if you could Bruce.
[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 10:48 am
Scott Solice (4/14/2009)
The article was excellent. It went a long way as an intro on the benefits of thinking set-based in SQL. The "technical" reasons were stated very clearly (eventually). I look forward to more.
thanks Scott!
[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 - 61 through 75 (of 380 total)
You must be logged in to reply to this topic. Login to reply