May 15, 2014 at 8:33 am
Cursors are bad in SQL Server and should be avoided unless there's absolutely no other way to achieve a task.
However, cursors are fine or even good in other databases and platforms.
Does anyone have a good resource that explains what it is about SQL Server that makes cursors bad? Is it a tradeoff where optimizing for something else causes this? Just a design philosophy? Something else entirely?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 15, 2014 at 8:44 am
SQL Server has a very bad implementation of cursors. I have no idea what exactly makes them so slow compared to Oracle cursors.
That said, regardless of the fast or slow implementation, cursors are bad because they make use of a fixed procedural execution strategy.
The main point in favor of set-based code is its declarative nature, which allows the optimizer to choose the most appropriate and efficient strategy to execute against the database.
Does this answer your question or did I misunderstand it?
-- Gianluca Sartori
May 15, 2014 at 8:49 am
I was more wondering what was done when creating SQL Server to make this so as opposed to Oracle or other systems where cursors are relatively fast. What was the decision making process like for this? What gains are there from taking this approach as opposed to systems that design so cursors work quickly.
Partly so the next time someone starts telling me "The old mainframe database I used back in diggity six was blazing fast when using cursors! SQL Server is terrible!" I have a better response than just how to do things in SQL Server so that you don't have those problems.
Partly for my own curiosity.
That said, part of your answer does indeed help. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 15, 2014 at 9:34 am
To a degree, what people think of as cursors in Oracle (and maybe in other DBMS, I'm not sure) are two things, one of which we have in SQL Server. They're both row-by-row processing and a data access method. In SQL Server, the data access method cursors are hidden within the execution plans, Merge and Hash and Nested Loops are actually cursors too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2014 at 9:46 am
I know there are a lot of cursors behind the scenes in SQL Server, but wasn't sure why those are good while the ones we write are bad. Figured it was some kind of optimizing they could do while creating the system.
So there are different kinds of cursors, that helps a little. Is there a resource that explains the difference?
Sorry to be asking for resource suggestions, Google gets swamped with "Cursors are bad" and "How to do cursor" results when I try to search.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 15, 2014 at 10:00 am
No, i'm sorry Stefan, I don't have a specific resource on that one. It's something that came out of multiple talks with Oracle people to get an understanding of what they're cursors do and why they use them so much.
Hang on, there might be a webinar over on Red Gate Oracle product page where we recorded one of the discussions.... Hmmph, can't find it, but there was one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2014 at 10:03 am
Either google-fu is failing me or I'm delusional about the recording of the discussion (both possible). Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2014 at 10:12 am
Stefan Krzywicki (5/15/2014)
"The old mainframe database I used back in diggity six was blazing fast when using cursors! SQL Server is terrible!"
Hmmm, yeah.
I had the chance to optimize some code in DB2/AS400 a couple of times. While cursors are blazing fast there, I can tell for sure that set based code is faster there as well 🙂
So, those who claim cursors are good in other platforms are just plain wrong. Cursors may suck less, but still suck.
-- Gianluca Sartori
May 15, 2014 at 10:55 am
Thanks Grant, I appreciate your looking. I'll try some other searches later, both on and offline, when I have a little more time.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 15, 2014 at 10:57 am
spaghettidba (5/15/2014)
Stefan Krzywicki (5/15/2014)
"The old mainframe database I used back in diggity six was blazing fast when using cursors! SQL Server is terrible!"Hmmm, yeah.
I had the chance to optimize some code in DB2/AS400 a couple of times. While cursors are blazing fast there, I can tell for sure that set based code is faster there as well 🙂
So, those who claim cursors are good in other platforms are just plain wrong. Cursors may suck less, but still suck.
Good to know. : -) Unfortunately, the people I'm talking about have a tendency to insist their anecdotes are absolutely right and dismiss whatever you say. That's why I was hoping for resources so I could barrage them with details. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 15, 2014 at 5:25 pm
spaghettidba (5/15/2014)
Stefan Krzywicki (5/15/2014)
"The old mainframe database I used back in diggity six was blazing fast when using cursors! SQL Server is terrible!"Hmmm, yeah.
I had the chance to optimize some code in DB2/AS400 a couple of times. While cursors are blazing fast there, I can tell for sure that set based code is faster there as well 🙂
So, those who claim cursors are good in other platforms are just plain wrong. Cursors may suck less, but still suck.
+1000. I'll also confirm that same finding for Oracle.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2014 at 5:29 pm
Stefan Krzywicki (5/15/2014)
spaghettidba (5/15/2014)
Stefan Krzywicki (5/15/2014)
"The old mainframe database I used back in diggity six was blazing fast when using cursors! SQL Server is terrible!"Hmmm, yeah.
I had the chance to optimize some code in DB2/AS400 a couple of times. While cursors are blazing fast there, I can tell for sure that set based code is faster there as well 🙂
So, those who claim cursors are good in other platforms are just plain wrong. Cursors may suck less, but still suck.
Good to know. : -) Unfortunately, the people I'm talking about have a tendency to insist their anecdotes are absolutely right and dismiss whatever you say. That's why I was hoping for resources so I could barrage them with details. : -)
I've found the best way is to find some troubled code in the system that uses cursors and rewrite it. Setup a couple million rows of test data and document the performance. Then show that to the anecdotal experts. Like they say, "One good test is worth a thousand expert opinions". And yeah, it's going to take a bit longer but it will be solid evidence that they cannot argue against. Even supposedly "professional" white papers can be argued against using the "here's a test you can run to verify" method.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2014 at 6:56 pm
Absolutely Jeff. My problem is that we're a SQL shop and he admits SQL cursors are horrible. I don't have access to other systems to show they're horrible there too. I'll probably just have to ignore it like when your drunk uncle starts going on about how much better cars were in the 70s.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 15, 2014 at 7:16 pm
Stefan Krzywicki (5/15/2014)
Absolutely Jeff. My problem is that we're a SQL shop and he admits SQL cursors are horrible. I don't have access to other systems to show they're horrible there too. I'll probably just have to ignore it like when your drunk uncle starts going on about how much better cars were in the 70s.
I don't have any white paper references but the internet is loaded with actual code examples (which, as previously stated, I think are much better than theoretical white papers). Here's one of my favorites because it's so simple.
http://it.toolbox.com/blogs/data-ruminations/the-curse-of-the-cursor-31851
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2014 at 7:27 am
Database engines don't execute T-SQL or PL/SQL; they compile to an execution plan and execute that. For example, in SQL Server two very different looking sql selects, one that references a view versus one that references a CTE, may actually compile to an equivalent execution plan. If you have coded a relatively simple cursor block, like a cursor that just iterates over a rowset aggregating sums, some database engines may actually be smart enough to compile this to the equivalent to a set based execution plan.
I don't know this for sure, so I'm just theorizing here, but if that's not the way it works, then that's the way it could conceivably work, and that would account for why one database engine outperforms another engine by an order of magnitude when given an equivalent batch of SQL code.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply