April 14, 2009 at 6:51 am
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.
April 14, 2009 at 7:00 am
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)
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
April 14, 2009 at 7:09 am
Gaby
I have to admit - I wasn't too kind on your original article. BUT - it did spawn what was possibly the most interesting thread ever on SSC. You should be proud! 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 14, 2009 at 7:25 am
Let me just make myself clear. I am not pro-cursor or pro-setbased or pro-whatever, I am pro-SQL Server and I don't have a set way of doing things. I always look at what would be the best way of doing a job. I just say that there is not one subject in SQL that is good or bad, it is up to you to make it good.
Barry, when are you going to give us your comments on what is being said here?
:-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 7:37 am
There is really nothing wrong with using cursors. If the application doesn't call for performance, YES cursors are quite easy to implement and everyone can read and understand them.
I myself have used them for ages now and found that they are quite useful at times. However, now that I am in a situation where my application is in need of the full potential of the SQL server, I cannot afford to use them.
The example posted, is just for you to measure the time that the query takes to run in your environment and to show what a resource hug a cursor could be. In my situation, the query ran for 20 seconds flat from the t-sql example provided.
After looking at the example cursor, I can see that this will generate a huge amount of rows with the crossjoin.
After recoding with a few select statement, I was able to make it run in less than 1 second.
That goes to show you that cursors are very costly to run.
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;-)
April 14, 2009 at 7:39 am
neufeldb4 (4/13/2009)
Allow *me* to make some observations.1: This article was insulting. *I* am an intelligent professional with many years of experience. *I* design my code thoughtfully and carefully and my projects are successful because of it. *I* read articles that challenge *me* to change my practices because *I* am always willing to learn and improve. This article was not in that category.
2: It is perfectly ok to use big words and get technical.
3: - 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.
If you insist on trivializing *my* work and insulting *my* intelligence, I will take note of that as well.
Brad Neufeld
Data Architect
I read the article and took note of the fact that it clearly specified that it was an *Introduction*. In the SQL World (as in the real one), the audience/reader ranges from the uninitiated novice to the know-just-enough-to-cause-trouble to the masters and the gurus. While not having actual statistics to fling around, a probable percentage split between these three categories would be 60-30-10
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.
I was just going to read the article and move on, but decided to take a few minutes more to read the discussions. This post bothered me so much that I felt compelled (read: moral imperative) to respond. Trivializing "your" work and "your" intelligence - poppycock! You can convey anything you want - and still maintain a level of professionalism and decorum. Since Mr. N didn't bother, I think it's only fair that I'm not bound by similar constraints. Mr N - the author did not spend hours over this article agonizing over how best to please you. I took the time to mark all the first-person personal pronouns you have used and am thankful that I have never had to work with anyone so full of himself and a sense of his own importance. In the SqlServerCentral world, you are one in a million - and that Mr.N - is fervent gratitude for the fact and *not* a compliment!
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!
**ASCII stupid question, get a stupid ANSI !!!**
April 14, 2009 at 7:42 am
gautamsheth2000 (4/13/2009)
Should use this code 🙂Select count(*) From master.sys.columns C1 CROSS JOIN master.sys.columns C2
Yes indeed. Good job!
[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 7:46 am
sushila (4/14/2009)
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!
I will volunteer too 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 14, 2009 at 7:55 am
select square(count(1)) From master.sys.columns C1
April 14, 2009 at 7:59 am
:-DMe three!
April 14, 2009 at 8:03 am
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
April 14, 2009 at 8:04 am
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. 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 agree you should try to avoid cursors as an easy fix if there's a better set based approach, but sometimes a cursor is the better approach. There are very very few absolutes in this world. I once heard someone say that if you use a cursor you don't know SQL. I put forth a real world problem to that person that I could find no way to accomplish without using a cursor. I never heard back from them
"In theory, practice and theory are the same. In practice they are not"
April 14, 2009 at 8:05 am
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.
I think the poster's comments (Brad) were indulgent and rude. I don't think the author intended to insult Brad or anyone else. If the poster has all the answers about cursors then why would he read the article in the first place? Only to be insulted?
I guess you're either on one side or the other when it comes to using cursors. It's rare that I've seen anyone on the pro-cursor side. Doesn't make much sense to me.?!?
Cursors had their time and place. But so did the bandaid I took off my finger last night. The gig is up!
April 14, 2009 at 8:14 am
bruce.trimpop (4/14/2009)
I put forth a real world problem to that person that I could find no way to accomplish without using a cursor. I never heard back from them
I don't suppose you remember it do you? I'd love to have a crack at it if you do 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 14, 2009 at 8:22 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
Okay, I'll bite. This may not be the best way, but it eliminates the looping functions and cursor (and, like others, I'll be happy to see improvements on this).
I'm assuming that the select your cursor is based on gets just the info needed to send out the email.
declare
@sqlcmd varchar(max), -- allows plenty of room for the sql statements to be run
@crlf char(2)
set @sqlcmd = ''
set @crlf = char(13) + char(10)
set @sqlcmd =
(select 'execute msdb.dbo.sp_send_dbmail ' + 'put your db_mail options here based on fields in the selecting tables/views' + @crlf
from table(s) --in the original select that the cursor is run off of
FOR XML PATH('') ) -- use for xml for faster string concatenation
execute (@sqlcmd)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 16 through 30 (of 380 total)
You must be logged in to reply to this topic. Login to reply