When To Use Cursors
-
here's mine
EXEC master.dbo.xp_execresultset N'select ''dbcc dbreindex (''+name+'')'' from dbo.sysobjects where xtype=''U''','bracknell'
where bracknell is the name of the database to run the command in.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I love it. Congratulations, Colin!
Before anyone else gets too fond of Xp_Execresultset, remember that it was undocumented and is not there in SQL Server 2005, though it was in 2000 and v7. Adam Machanic has produced a substitute (called ExecResultSet). See his blog on
so if you use that, then Colin's wonderful code should work fine.
I did my own version of the 'non-cursor challenge' rather hurriedly and left in a bit of diagnostic code which is easy to spot. Obviously, there is a limit on the number of tables you can reinded with the technique on SQL 2000 due to the 8000 varchar limit but it should work with 2005 with a varchar (Max) - It is possible to get round the 8000 character limit on SQL 2000 as Execute () doesn't have the 8000 character limit. Ken Henderson has an amusing solution in his 'Guru...architecture..internals' tome.
Best wishes,
Phil Factor -
95% of the time, Michael's right:
If you really, really, can't find a way to do it in SQL other than using a cursor, stop right there, give your head a shake and pass the problem on to the application. Sequential operations are much more efficiently processed outside of SQL, if they cannot be solved using set based queries.
The other 5% is for trivial apps that run infrequently and process few records, and then it equates to *your* convenience and ease of maintenance, not optimal server handling. As always, there's a fine balance between idealism & practicality (hence this discussion), but if you form good habits when working small, they'll save your a** when you're working big.
-
Hmm .. guess you'd fail the mcdba questions on server vs client cursor processing. Using a client cursor ( and looped processes are still cursors even if in disguise 
and issuing sequential commands from a client will likely not be any more efficient ( think network traffic, packets etc. ). The original point was the issue of sequential processing and the fact that sometimes you actually need to do such operations. To be blunt about it saying ... shake your head .. etc. etc. -- you're still using a cursor albeit not on the sql server, so not a solution. Actually I figure using the CLR would be the way to go ( if you wanted to take this approach )Of course I like client cursors, they're generally a serious performance issue and as I tune apps/servers for a living please keep writing client cursors, it'll keep me in work!
I tried to think off hand of a "non-trivial" instance of such an operation, best I could think of was maintaining the current share price of stocks as an overnight process.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
When I need to use a cursor, if the dataset is not very large then I dump it to a local table variable and then perform a cursor operation on the local data (provided that it is a forward, read only scroll). This avoids locking the source table data.
That being said, I still try to find set-based solutions whenever possible.
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com -
I needed to get a cumulative running balance column in a table. So I used a cursor. I'm not very advanced but it seemed like the only way to look at a numeric column, then add that value to the row above. (I first set the running balance column equal to the amount column for row 1, and had my logic looking for all rows > 1.)
Further, I had to take my table and move the date value up 1 row (don't ask, it's about manually calculating interest on a bank account). So again, I used a cursor to loop through each row, setting the date column equal to the value in the row below.
Then, I had to set a 'days passed' value on each row, which was the difference in the date column's value to the date column in the row above. (Again, setting the row 1's date passed value to 1 and skipping that in the remaining logic.)
My final table looked like:
identity, date value, days passed, amount, cumulative balance
If anyone has any idea how to do those three things without a cursor, I'd really like to learn. Sharon.
-
Sharon
Have you read 'Robyn Page's SQL Server Cursor Workbench' on Simple Talk? http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/
It gives a pretty similar example, the 'running Total' problem. The 'Quirky update' approach seems to work well for large tables, especially when you specify the 'Order By' in the update statement!
Best wishes,
Phil Factor -
Thanks for the Robyn Page Simple Talk article, I'm reading it now. There's a difference between needing to calculate a running total on the fly and having to calculate it and save it in the data table. There's also a major difference between calculating it as you populate your table vs later on when the table already exists.
But there certainly seem to be lots of examples that cover lots of bases. I'll read through them all, but I do want to say, I tried doing what I needed to do without a cursor and all the darned thing would do is update the 1st row of data, rather than looping through all rows of data. But then I'm just a 'grasshopper' and perhaps I haven't been using my case when's as elegantly as I could have ...
-
Sorry, I' thought I'd understood that you wanted to save your calculations in the table. Robyn's article would show you how to do that. If you're not quite sure how it would work, post a reply and I'll try to help with a bit more detail. I think it would take two update statements but it will still be a lot simpler to do than a Cursor-based solution.
Best wishes,
Phil Factor -
Sorry, the notable word in my last post was 'update'. The running totals issue can be addressed in a number of ways outside a cursor. But what of my other two needs?
1) set the date on each row of data to be the date of the row below.
2) add a value which is the difference (in INT) between each row's date and the date on the row above.
Actually, the overall logic of 2) above probably falls into the running totals category of solutions. It's really a similar type of calculation.
But how 'bout actual updating of data? Again, I'm not so advanced, so if there's a way to do this without looping (cursor or not) I'd be very grateful to learn.
-
Sharon Matyk (1/3/2008)
2) add a value which is the difference (in INT) between each row's date and the date on the row above.This can be done with what I call an offset join, providing there's an easy way to tell which is the 'next' row. If for example you have an identity column, then this can work. (As a select statement, not update)
SELECT Tbl.ID, Tbl.TheDate, Offset.TheDate AS ThePreviousDate from
Tbl INNER JOIN Tbl AS Offset ON Tbl.ID = Offset.ID + 1
Be careful with this. Since there is a calculation on one side of the join, you are likely to get sub-optimal index usage. The couple of imes I've used this kind of technique it worked quite well. Just test carefully.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Merrill Aldrich (1/15/2007)
I also read that article and can't find it anymore :-).For anyone interested, I found the articles here: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/running-sums-redux.aspx
-
The place I've had to use iterative code in T-SQL is hierarchies. (I tend to use While instead of cursors, because I can have some set-based operations mixed in with step-by-step.)
In SQL 2000, there were no CTEs for that kind of thing, so something iterative was necessary.
In 2005, a CTE can handle this, but I have found one problem that I haven't been able to solve yet. What happens in a hierarchy structure that has a repeated element at more than one level?
For example: A dog breeder wants a database to keep family trees of his dogs, for pedigree purposes. In one bloodline, he wants to lock in certain recessive traits, so he breeds a female dog to one of her own offspring. She thus shows at two levels of the hierarchy, and creates a recursion problem in the CTE.
I haven't been able to work out how to show this data in a recursive-CTE hierarchy query without getting a recursion error or a truncation problem. I can do it in a While loop very easily, but not a CTE.
Anyone have any better ideas on how to handle repetitive hierarchy levels without using a cursor/while?
Also, I haven't tested this, but can a recursive CTE handle a hierarchy with more than 32 levels (or whatever the nested query maximum is)? If not, that would definitely not work for complex bills of materials. (I hadn't thought of that when I started writing. Now I'm going to have to go answer my own question on that one.)
- 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
-
Just tested a CTE with 101 levels of hierarchy, and it worked. No error.
- 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
-
Never mind on the repeating hierarchy. I figured it out. (I think.)
- 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
Viewing 15 posts - 61 through 75 (of 81 total)
You must be logged in to reply to this topic. Login to reply