November 17, 2009 at 11:27 am
I've read that some schools of thought on cursors are to never use them. I know that SQL Server now has the CTE. Which, I have used. However, I've found that when updating a dataset in a SQL statment, I use cursors. It is what I'm comfortable with. But, what is best practice?
Thanks for your thoughts.
November 17, 2009 at 11:32 am
Use cursors when you need to iterate through databases and tables where the command isn't something that can be written as a single T-SQL command.
For example, if you want to do a full backup of all online databases that are read-write enabled (not read-only ones), you'll need to query sys.databases and step through it to issue the backup command for each one. That's best done with a cursor.
Updating a dataset is something where a cursor is almost always a bad idea. You can almost always improve the performance by getting away from cursors on that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2009 at 1:07 pm
GSquared (11/17/2009)
Use cursors when you need to iterate through databases and tables where the command isn't something that can be written as a single T-SQL command.For example, if you want to do a full backup of all online databases that are read-write enabled (not read-only ones), you'll need to query sys.databases and step through it to issue the backup command for each one. That's best done with a cursor.
Updating a dataset is something where a cursor is almost always a bad idea. You can almost always improve the performance by getting away from cursors on that kind of thing.
Agreed, and "improving performance" above can in many cases be a dramatic improvement (say - 400 to 1). We recently cut a 3-day process to 2 hours simply by taking it to a set-based process instead of a cursor.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 17, 2009 at 2:11 pm
Personally the only cursors i have written recently are when my actions are outside of the transactional 'world' of sqlserver, Such as sending emails.
November 18, 2009 at 7:54 am
I'd agree with the posts thus far. Cursors are often necessary for administrative tasks (backups, index maintenance, etc...), but rarely if ever needed for data retrieval and modification.
If you think you need a cursor, post the problem on SSC and I be someone will have a way to do it without a cursor.
Also check out R. Barry Young's series (2 parts so far) on 15 Ways to Lose Your Cursors. Part 1 here[/url]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 18, 2009 at 8:00 am
Interesting description of a cursor..."the bastard love child..."
Thanks for all your feedback. I definitely get a sense everyone's negative views on the use of a cursor. The next time I think I need one, I'll try to avoid it.
November 18, 2009 at 8:30 am
dmounday (11/18/2009)
Thanks for all your feedback. I definitely get a sense everyone's negative views on the use of a cursor. The next time I think I need one, I'll try to avoid it.
If you get stuck you know where we are đŸ˜‰
November 18, 2009 at 9:13 am
Stuck? I'm there everyday. Today it is setting up a reporting model. I found the best documentation on the subject is all the error messages I get when trying to deploy the model. Microsoft is getting hate mail from me on this.
November 18, 2009 at 4:01 pm
Hi,
What would you recommend instead of cursors? To use a table variable to store the data to process and have a while to use that data?
declare cur cursor local fast_forward for SELECT col1, col2, ..., coln FROM table INNER JOIN ... WHERE...
open cur
fetch next from cur into @var1, @var2, ..., @varn
while @@fetch_Status = 0
begin
....
fetch next from cur into @var1, @var2, ..., @varn
end
close cur
deallocate cur
and replace it with
declare @t table (col1 .., col2 .., ..., coln ...)
insert into @t select col1, col2, ..., coln, 0 FROM table INNER JOIN ... WHERE
SELECT TOP 1 @var1 = col1, @var2 = col2, ..., @varn = coln FROM @t
WHILE @var1 IS NOT NULL
BEGIN
....
DELETE FROM @t WHERE col1 = @var1 AND .... --key columns
SET @var1 = NULL
SELECT TOP 1 @var1 = col1, @var2 = col2, ..., @varn = coln FROM @t
END
is this a good approach or is there a better way?
Thanks,
Pedro
November 18, 2009 at 4:14 pm
Pedro,
The answer is it depends on what you are doing. Replacing a cursor with a while loop is still not creating a set-based solution. SQL Server is designed to work with sets of data and if you provided a specific example of what you are doing within the loop someone would be able to show you a set-based way to accomplish your task or confirm that a loop is the best solution.
If you have a specific problem you are trying to solve start a new thread for that specific problem. You could also read the articles I referred to in my first post.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 19, 2009 at 1:13 am
In our app, which is has vertical database, clients can customize the views to display data.
We have a field's table, view's table and viewfield's table, and of course, N data tables for each different data type.
Fields:
FieldId, NameToDisplay, Type, DataTable, ....
Views:
ViewId, ViewName, Order, ...
ViewFields:
ViewId, FieldId, FieldOrder
Data:
RecordId, FieldId, Value, ...
This is a very "simple" version since our fields can refer values stored on other fields..
So, when ever a view is change we calculate the query for that view, to store it like "cache" so it doesn't have to be calculated every time someone uses the view.
We have to build a query do display the data. We have to go to every field on the view and build the query.
SELECT f.FieldId, f.NameToDisplay, f.DataTable FROM Field f INNER JOIN ViewFields vf ON f.FieldId = vf.FieldId WHERE vf.ViewId = @ViewId
For each record return above we have to build a query:
SET @query = @query + ', (SELECT TOP 1 Value FROM ' + @DataTable + ' data WHERE FieldId = ' + @FieldId + ' AND data.Id = root.Id) AS [' + @NameToDisplay + ']'
In this case I don't think we can "escape" the Loop or a Local fast_forward CURSOR, or is there another way to do this?
Thanks,
Pedro
November 19, 2009 at 7:08 am
If I'm correctly understanding the design, yes, you should use cursors for that. It's what's known as a "one true lookup table" solution, and it's a complete violation of what relational databases are meant to do. Since you'll never be able to get scalable, top speed performance out of it anyway, a few cursors aren't going to matter much.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2009 at 8:44 am
Thanks...
The vertical database was the solution we found for having 100% customization by our customers.
they can create their fields as they need, add them to views, design the form's layout, etc, etc...
It's a "bit" heavy but was the best solution we found at the time... Sharepoint uses a structure very much like the one we use.
Pedro
November 30, 2009 at 4:12 pm
I must preface this post w/ the fact that I hate cursors. The simple fact I must allocate and deallocate something drives me batty and if you have embedded transactions inside them they can cause severe headaches. I get into the "use cursor or not" w/ my colleagues all the time. One of the best examples that I’ve found as an argument against using a cursor is as stated. When using a cursor you cannot manipulate the actual set driving the looping functionality.
example. farfetched but still valid.
You have a cursor that is loaded w/ all your customers. It has custid, age, height, addr, zip etc
You want to step through each and perform some action. This process runs every 5 mins say for argument sake.
Cursor
If you use a cursor u are required to loop each and every record no matter what. Sure you can check some value in the loop and pending the result you can move to the next iteration of the loop without really doing anything. However you cannot manipulate the original set the loop is looping though and in turn lessen the number of actual loops u do dynamically at runtime.
What if u have something in the loop that says if it's the first Tuesday on the month then you don’t need to process any of the customers who are under 5 feet tall and say if it's Wednesday and getdate() says it's between two and ten seconds after the hour then you don't need to process any customers who are over 40 years of age because of some promotion. With a cursor you would still have to loop and do some compare and then a next or something to iterate to the next record.
noncursor
If you implement a non cursor loop which was running off a temp table w say a processing flag then in your loop you can manipulate the actual set the loop is running on and actually change its course. For example. Once you get to the loop of a record that has a person who is under 5 feet tall you can then update all the people in the temp table who are under 5 feet tall to have a processed flag of 1 and therefore never actually loop for these records. I know it's farfetched but the point is still valid. W/ a cursor you must loop through each record no matter what and if you don't use a cursor you can manipulate the looping mechanism and make changes to the number of iterations at runtime dynamically.
The next topic is the performance difference between skipping to the next record in the cursor vs setting a processing flag in a temp table.
Making any sense
December 1, 2009 at 4:15 am
Hi John
You could exclude those people from being processed by changing the query that is the source of your cursor, i.e. select all where they are not 5 feet tall.
So that is a bit of a spurious argument đŸ™‚
In my experience, a fast-forward cursor will outperform a loop that updates the underlying table, sometimes significantly, especially if you are using a variable table (@process) and looping.
Of course, as stated elsewhere in this thread, set-based operations are the way to go. However in the rare case that you need to process records sequentially, a cursor is the tool provided by SQL server to do that, and does the best job of iterating records for processing.
Regards,
Joon
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply