Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sapator
Constraint Violating Yak Guru
462 Posts |
 Posted - 2011-02-04 : 18:39:56
|
| I went to a company for an interview and to my surprise they were still using cursors in sql server.Now i'm not the best sql programmer around and i think i have skipped the cursors part(due to younger age) but i think some of you guys can give some info on this one.Are cursors needed nowadays and what is a job that only a cursor can do(as i was told when i asked why they use them(they didn't elaborate).Thanks. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-04 : 19:21:29
|
Cursors are for people who don't know who how to think "Set based". In some cases, they may be required, but in general..they should be avoided. More often than not, a set based solution can be achieved. People have a habit to think about what they want to do to a ROW of data instead of what they want to do to a set of data.I am sure there are instances where a cursor IS required...just can't think of any off hand that a set based solution wasn't far better. most people just assume that "if I want to do something to each of these rows, I need to do it one row at a time"..when that often isn't the case.Set based > While Loop> Cursor. Cursor is the last choice, at least for me. At work, a coworker was working on a complex string parsing cursor that was around 300 lines long. I showed him a 5 column select statement that did the same thing...sometimes people just don't think it can be done without a cursor.Part if it, I think, comes from other languages or methods that are "row by row" type operations. Some ADO recordset operations in VB come to main. Mainframe SAS programs come to mind...I find it a distinct mindset difference about how one approaches the data. If you think in Set based ..you generally don't even consider a cursor. Are the needed? maybe, but they are surely not needed everywhere they are currently used.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-02-04 : 19:52:39
|
| I have the same thoughts as you do but i'm not a sql guru so i'm just reading what expert people write.I told them (when they said they use cursors) that this is an old and bad habit and they told me that even "select" is a cursor itself.So i thought that either i'm not well educated or that they are idiots. :) |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-04 : 19:57:49
|
Well, you likely made a mistake criticizing their practice of using them by saying it was an old and bad habit. A better approach is to say that "in many cases, there may be opportunities to leverage set based solutions, which could yield performance benefits over cursors". Key point..not criticizing them.A SELECT could be seen as a cursor, if one doesn't think Set-Based. There are way more people here, actually qualified as an expert, so hopefully they weigh in.here is an interesting thread on the topic http://stackoverflow.com/questions/24168/why-are-relational-set-based-queries-better-than-cursorsI am sure the experts here have written many as well.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-02-04 : 20:03:48
|
| The uses I've found for cursors are times where I do in fact want to parse a set of data row by row, for example some of the results in your cursor will have an impact on other rows in the same cursor. Sometimes it just makes more sense in a case like that to use a cursor instead of trying to come up with a complex update/insert command involving a lot of sub-queries. There might always be cases where performance wise it makes more sense to parse a lot of small queries instead of trying to come up with a one big command that could potentially lock your tables up.For example one cursor i use parses a temp table and runs various lookups/calculations to insert them into another table. In theory it could be done with a single insert statement but it would be much much harder to read/modify at a later date. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-02-04 : 20:13:26
|
| Thanks again guys.Also i market the article down for read tomorrow cuz i have to duplicate some tables right now.Also dataguru1971 you are right about my behavior i've been to many interview but it was 8 in the morning and i was having a lecture in how cursors do everything nicely.So i cracked :PBottom line, i have a second interview with them on Monday so i guess it didn't matter to their ego what i said :)Either that or the fact that i worked for a doze insurance companies here (they are one). |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-04 : 20:15:56
|
Well, good luck. Focus on what you can help them improve, not what they do wrong.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-04 : 20:19:56
|
quote:
Originally posted by ZZartin
For example one cursor i use parses a temp table and runs various lookups/calculations to insert them into another table. In theory it could be done with a single insert statement but it would be much much harder to read/modify at a later date.
Ease of readability is subjective though. I can sort out a set based procedure a lot faster than a cursor. I bet a CTE and a set based solution would be pretty easy to follow..Keeping in mind of course, that even set based solutions can manifest with hidden RBAR (term courtesy of Jeff Moden). I have seen that mistake too, which can often result in people thinking a cursor works "better" when it was only the RBAR that made the set based solution seem ineffective.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-05 : 08:58:49
|
A recordset is sometimes referred to as a cursor -- though not when we're talking about SQL Server. This is probably why they told you a select is a cursor.quote:
Cursors are for people who don't know who how to think "Set based".
I hate when people say things like this. Or "SSIS is useful if you don't know SQL."A place and time for everything (except cascading keys  ).Suppose I asked you for a list of every user table in every database along with the number of rows, reserved KB, data KB and index size. How will you do that without a cursor? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-05 : 10:08:42
|
I still stand by my "put sugar in coffee using a spoon or tweezers" analogy regarding cursors. More babble about it here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47319http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72457http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118835 id="quote">quote:
In theory it could be done with a single insert statement but it would be much much harder to read/modify at a later date.
I have found the opposite to be true. I can NEVER figure out what's being done in a cursor procedure, especially if it can be done with a single SQL statement. My reply in the first link above has an example of that. I've also found cursor code to be more verbose. I understand why people hesitate to learn set-based thinking, especially when they've started with iterative/procedural programming. I've been there, I've melted my brain on it, I hated it at first too. But once I got it, it's made all of my programming skills better, even non-SQL code. While it's a good practice to break larger problems into smaller pieces, SQL Server cursors are not the right way to do that. All they add is navigational overhead that gets in the way of solving the problem.quote:
Sometimes it just makes more sense in a case like that to use a cursor instead of trying to come up with a complex update/insert command involving a lot of sub-queries.
Unfortunately that way of thinking is too pervasive, and unless you deliberately break away from it, you'll forever struggle to use SQL effectively. Take the extra time to do it in a set-based way, even if you're under pressure to get it done quickly. And I'll think you'll find the set-based method is easier and less complicated to code, once you get your mind thinking that way. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-05 : 10:24:23
|
Taking a general opinion out of context I see. I never meant to imply that they never needed to be used, only that many times they are used--there are set based options available which are better. My opinion is based on personal experience where everyone thinks "row by row" instead of set based. It was just a general comment is all..:)Obviously there are places where cursors are useful. However, I dug this out of my stuff when I was messing around with CTE's. It appears to produce correct data at a glance, but never really picked it up again. Not a cursor though..again, not perfect, was just messing around a while back with 2005 when I was trying to figure out uses for CTE's and trying to learn. Obviously, can't do every database without a cursor or sp_MSFOREACHDB in your question. ;WITH usertables as ( SELECT id from sysobjects where xtype = 'U' ) ,reserved as ( SELECT id,SUM(reserved)-SUM(isnull(used,0)) as unused,Resv=SUM(reserved) FROM sysindexes Where indid in (0,1,255) Group by id ) ,data as (SELECT id,SUM(case when indid <2 then dpages else 0 end) as pages, SUM(Case When indid = 255 then isNull(used,0) else 0 end) as usedpages FROM sysindexes Where indid <2 Group by id ) ,indexes as (select id,sum(used)as iused from sysindexes where indid in (0, 1, 255) group by id ) ,row as ( Select id,rows from sysindexes where indid < 2 ), spaceused as (Select object_name(u.id) as tablename ,data=pages-usedpages ,reserved = resv ,iused = iused-pages-usedpages ,rows ,unused FROM usertables u,reserved s,data d, indexes i,row r WHERE u.id = d.id and u.id = s.id and u.id = i.id and u.id = r.id and i.id = s.id and r.id = s.id and r.id = u.id )SELECT tablename ,reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB') ,dataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB') ,indexKB = ltrim(str(iused * d.low / 1024.,15,0) + ' ' + 'KB') ,unusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')FROM spaceused,master.dbo.spt_values dwhere d.number = 1 and d.type = 'E' order by data * d.low / 1024 desc
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-05 : 10:34:20
|
quote:
Originally posted by robvolk
I still stand by my "put sugar in coffee using a spoon or tweezers" analogy regarding cursors. More babble about it here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47319http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72457http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118835 id="quote">quote:
In theory it could be done with a single insert statement but it would be much much harder to read/modify at a later date.
I have found the opposite to be true. I can NEVER figure out what's being done in a cursor procedure, especially if it can be done with a single SQL statement. My reply in the first link above has an example of that. I've also found cursor code to be more verbose. I understand why people hesitate to learn set-based thinking, especially when they've started with iterative/procedural programming. I've been there, I've melted my brain on it, I hated it at first too. But once I got it, it's made all of my programming skills better, even non-SQL code. While it's a good practice to break larger problems into smaller pieces, SQL Server cursors are not the right way to do that. All they add is navigational overhead that gets in the way of solving the problem.quote:
Sometimes it just makes more sense in a case like that to use a cursor instead of trying to come up with a complex update/insert command involving a lot of sub-queries.
Unfortunately that way of thinking is too pervasive, and unless you deliberately break away from it, you'll forever struggle to use SQL effectively. Take the extra time to do it in a set-based way, even if you're under pressure to get it done quickly. And I'll think you'll find the set-based method is easier and less complicated to code, once you get your mind thinking that way.
Yeah, what he said. I LOL'd at brick wall example...that is a good one!
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-05 : 11:38:27
|
I agree that the need is rare. Sorry if I took your remark out of context.By the way, sp_msforeachdb uses a cursor.  |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-05 : 16:03:44
|
quote:
Originally posted by russell
I agree that the need is rare. Sorry if I took your remark out of context.By the way, sp_msforeachdb uses a cursor.
LOL. No doubt, but at least I don't have to write it.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-05 : 17:55:02
|
| LOL good point.Once again, sorry. Didn't mean to be a, well you know what, lol.Nice work on the query above. I restore a whole bunch of production databases nightly to a sandbox server. Serves two purposes: (1) to make sure the backups are good and (2) so the business analysts can play with some real data. As an added bonus, it gives me the ability to monitor growth simply by iterating through every database and capturing the size of the objects. I used sp_spaceused to do it.Compared the results of mine to yours, and they return the same results. Yours might be more efficient (though I would tweak it slightly). Because I want every db, and every table in one shot, I'm forced to use a cursor, a temp table and dynamic SQL. Granted, it runs super fast (and on high end hardware) but it is NOT the picture of beautiful, optimized code! |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-05 : 18:31:00
|
Thanks!..it was really something from maybe 18 months ago or even longer when I was looking at procs I found with cursors and tried finding other ways. One of the procs was that sp_spaceusedbytable and it seemed an easy one to play with. I never really went back and cleaned it up...looking at it today, and what I have learned since..I could make it better. I never compared execution plans or anything on it, but I would think it "might" be more efficient but I never put in practice. Might be useful as a view or table valued function in each db though with some tweaks though..then you could just select the records from the view into your temp table (or table variable?) I didn't take offense. If you do tweak it and use it, let me know ...you can steal it if you like.Just happened to have that handy when you posed the question (aside from the "every database" part..)edit: results do match, but a quick re-scan shows that one of the CTE's has a where clause that doesn't jive with the select..,data as (SELECT id,SUM(case when indid <2 then dpages else 0 end) as pages, SUM(Case When indid = 255 then isNull(used,0) else 0 end) as usedpages FROM sysindexes Where indid <2 or indid = 255 ---missed this. Group by id )
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-02-05 : 18:49:27
|
| Useful info guys.Thanks.All post something for the "go" statement in a new thread if you don't mind. |
|
|
|
|
|
|
|
|
using cursors in general, discussion.