Are the posted questions getting worse?
-
Lynn Pettis (9/18/2015)
SQLRNNR (9/18/2015)
Jack Corbett (9/18/2015)
Okay, I'm going to give the instructor the benefit of the doubt. Maybe lesson 1 is cursor and lesson 2 is set-based and there will be a discussion of why set-based is better.Or the prof came from an oracle background?
Or from a dev background?
My one year on the dark side, all the queries I wrote were set-based. The only time I needed a cursor was when I needed to return a result set back to the application. That was done using a reference cursor.
Maybe, but my understanding is that Oracle was written and optimized to favor cursors over sets. SQL Server is the opposite of that (well despite the massively tuned internal cursors like nested loops).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (9/18/2015)
Lynn Pettis (9/18/2015)
SQLRNNR (9/18/2015)
Jack Corbett (9/18/2015)
Okay, I'm going to give the instructor the benefit of the doubt. Maybe lesson 1 is cursor and lesson 2 is set-based and there will be a discussion of why set-based is better.Or the prof came from an oracle background?
Or from a dev background?
My one year on the dark side, all the queries I wrote were set-based. The only time I needed a cursor was when I needed to return a result set back to the application. That was done using a reference cursor.
Maybe, but my understanding is that Oracle was written and optimized to favor cursors over sets. SQL Server is the opposite of that (well despite the massively tuned internal cursors like nested loops).
It's actually a bit of a myth, IMHO. I worked with Oracle for 3 years and never had to write a cursor. In fact, one of the responsibilities I had was to make performance improvements to code. While the cursors did appear to work better than those in SQL Server, good set-based code still blew the cursors away.
Triggers were a bit of an oddity because you couldn't actually write a set-based trigger or so it seemed. If you wanted a trigger to work with more than one row, you had to use variables and you had to use FOR EACH ROW as part of the CREATE TRIGGER statement... and they absolutely screamed.
{EDIT}... almost forgot. I did write some cursors but only because you couldn't pass Oracle result sets back to a GUI directly. Instead, you had to use "reference cursors". I found a handy tool for assembling packages of related code in the form of "Global Reference Cursors" and those worked very well. I'm pretty sure they operated at the machine language level because they were a whole lot faster than you could imagine a regular cursor being.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Is anyone memory optimized?
😎
-
Guessing that won't work out too well. We all have to keep in mind that those who can't do, teach.
I remember reading an interesting alternative take on that axiom once. It basically said that once upon a time the full quote was 'those that cannot do any more teach.' The logic being that those who are too old or infirm to physically do a task pass their knowledge on to others that can. Apparently the SAS keep on wounded servicemen because of the valuable experience that they have. While they may no longer be able to burst through a window or build an IED themselves, they can pass on the best way to do it to the new recruits.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Eirikur Eiriksson (9/20/2015)
Is anyone memory optimized?😎
Not me. But now that I've seen that thread, I am curious.
-
Brandie Tarvin (9/21/2015)
Eirikur Eiriksson (9/20/2015)
Is anyone memory optimized?😎
Not me. But now that I've seen that thread, I am curious.
IIRC Paul White has one or two articles which exploit (and explain) this behaviour. Looking now...
Edit: found one.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Jeff Moden (9/18/2015)
SQLRNNR (9/18/2015)
Lynn Pettis (9/18/2015)
SQLRNNR (9/18/2015)
Jack Corbett (9/18/2015)
Okay, I'm going to give the instructor the benefit of the doubt. Maybe lesson 1 is cursor and lesson 2 is set-based and there will be a discussion of why set-based is better.Or the prof came from an oracle background?
Or from a dev background?
My one year on the dark side, all the queries I wrote were set-based. The only time I needed a cursor was when I needed to return a result set back to the application. That was done using a reference cursor.
Maybe, but my understanding is that Oracle was written and optimized to favor cursors over sets. SQL Server is the opposite of that (well despite the massively tuned internal cursors like nested loops).
It's actually a bit of a myth, IMHO. I worked with Oracle for 3 years and never had to write a cursor. In fact, one of the responsibilities I had was to make performance improvements to code. While the cursors did appear to work better than those in SQL Server, good set-based code still blew the cursors away.
Triggers were a bit of an oddity because you couldn't actually write a set-based trigger or so it seemed. If you wanted a trigger to work with more than one row, you had to use variables and you had to use FOR EACH ROW as part of the CREATE TRIGGER statement... and they absolutely screamed.
{EDIT}... almost forgot. I did write some cursors but only because you couldn't pass Oracle result sets back to a GUI directly. Instead, you had to use "reference cursors". I found a handy tool for assembling packages of related code in the form of "Global Reference Cursors" and those worked very well. I'm pretty sure they operated at the machine language level because they were a whole lot faster than you could imagine a regular cursor being.
Lynn and Jeff, I worked in Oracle for 15 years and I can say that reference cursors was the way of writing your output to the screen and they were very fast. I know I'm dating myself a bit here, but the last upgrade we talked about was 9i, so I don't know what's changed since then. The FOR EACH flied back then.
-
SQLRNNR (9/18/2015)
Jack Corbett (9/18/2015)
Okay, I'm going to give the instructor the benefit of the doubt. Maybe lesson 1 is cursor and lesson 2 is set-based and there will be a discussion of why set-based is better.Or the prof came from an oracle background?
Or from a dev background?
Aye, I was thinking the same with the SHOUTY_CURSOR approach. Or maybe they finally cancelled his IDMS COBOL class.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
The homework assignment may dictate that the problem be solved with a cursor. Anyone who is good with cursors want to take a look at this?
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Alan.B (9/21/2015)
The homework assignment may dictate that the problem be solved with a cursor. Anyone who is good with cursors want to take a look at this?Anyone good with cursors that's willing to admit to it?:w00t::w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (9/21/2015)
Alan.B (9/21/2015)
The homework assignment may dictate that the problem be solved with a cursor. Anyone who is good with cursors want to take a look at this?Anyone good with cursors that's willing to admit to it?:w00t::w00t:
LOL. And using a cursor for what they are trying to do is a t-sql hammer. Sweet. I get to repost an awesome graphic from Lowell twice in about a week.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
WayneS (9/21/2015)
Alan.B (9/21/2015)
The homework assignment may dictate that the problem be solved with a cursor. Anyone who is good with cursors want to take a look at this?Anyone good with cursors that's willing to admit to it?:w00t::w00t:
Someone who is good with cursors should be able to know where they shouldn't be used.
On the other hand, should someone who's good in cursors be called a curser?
-
SQLRNNR (9/18/2015)
Lynn Pettis (9/18/2015)
SQLRNNR (9/18/2015)
Jack Corbett (9/18/2015)
Okay, I'm going to give the instructor the benefit of the doubt. Maybe lesson 1 is cursor and lesson 2 is set-based and there will be a discussion of why set-based is better.Or the prof came from an oracle background?
Or from a dev background?
My one year on the dark side, all the queries I wrote were set-based. The only time I needed a cursor was when I needed to return a result set back to the application. That was done using a reference cursor.
Maybe, but my understanding is that Oracle was written and optimized to favor cursors over sets. SQL Server is the opposite of that (well despite the massively tuned internal cursors like nested loops).
I think that's a combination of
1) their rules based optimiser in the (very) old days before they bought DEC RDB where by all accounts it would produce some horrible plans. Don't know how valid this is but I saw one tuning hint from about 1990 where rather than AND 3 columns in a WHERE, do something like WHERE (IF col = value THEN 1 ELSE 0) * (IF col2 = value2 THEN 1 ELSE 0) * (IF col3 = value3 THEN 1 ELSE 0) = 1 as ANDing would cause 3 scans and the second way would force a row by row evaluation and 1 scan. Don't know how valid that actually was, but if so a cursor would probably perform ok against the plan in scenario 1
2) Salesweasels playing down the cost of retraining "Because cursors are just like COBOL, so you can use all your dusty old COBOL coders for no extra cost!"
Certainly in a previous shop (8i) they'd evaluated cursor v set based and set based blew cursors out of the water.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
Anyone know what the heck is going on with the subscription emails today?
Usually I get a nice compact THIS:
Hi Brandie Tarvin,
A new reply has been added by Steve Jones - SSC Editor to a topic you're subscribed to.
You can view the reply by clicking the link below...
http://www.sqlservercentral.com/Forums/FindPost1720997.aspx
You can unsubscribe from topics at anytime from your control or the following link...
http://www.sqlservercentral.com/Forums/TopicSubscriptions1.aspx
You may need to manually copy & paste the URL into the address bar of your browser if the text above is not linked. Please watch for the URL wrapping onto 2 lines.
-----------------------------------------
SQLServerCentral.com
All day today, I'm getting THIS:
Hi Brandie Tarvin,
A new reply has been added by Alan.B to a topic you're subscribed to.
You can view the reply by clicking the link below...
You can unsubscribe from topics at anytime from your control or the following link...
You may need to manually copy & paste the URL into the address bar of your browser if the text above is not linked. Please watch for the URL wrapping onto 2 lines.
-----------------------------------------
-
Brandie Tarvin (9/21/2015)
Anyone know what the heck is going on with the subscription emails today?Usually I get a nice compact THIS:
Hi Brandie Tarvin,
A new reply has been added by Steve Jones - SSC Editor to a topic you're subscribed to.
You can view the reply by clicking the link below...
http://www.sqlservercentral.com/Forums/FindPost1720997.aspx
You can unsubscribe from topics at anytime from your control or the following link...
http://www.sqlservercentral.com/Forums/TopicSubscriptions1.aspx
You may need to manually copy & paste the URL into the address bar of your browser if the text above is not linked. Please watch for the URL wrapping onto 2 lines.
-----------------------------------------
SQLServerCentral.com
All day today, I'm getting THIS:
Hi Brandie Tarvin,
A new reply has been added by Alan.B to a topic you're subscribed to.
You can view the reply by clicking the link below...
You can unsubscribe from topics at anytime from your control or the following link...
You may need to manually copy & paste the URL into the address bar of your browser if the text above is not linked. Please watch for the URL wrapping onto 2 lines.
-----------------------------------------
This might be something in place on your end, Brandie. The links in my notifications look normal.
Viewing 15 posts - 50,656 through 50,670 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply