When To Use Cursors
-
Yes, a storm indeed. But a surprisingly pleasant one this time. With experience I see more gray and less black/white in the world, and I think that opens some interesting doors.
If anything, it should be "looping is evil" rather than "cursors are evil", because we should all prefer a set based solution as long as it is reasonably achievable and performs ok. I think we're failing as a group not to see that loops are loops. The question is, can I (or any of you) make a compelling case for the Zen state required to use loops appropriately? Not sure I did, but combined with all the comments in the thread maybe its something we can point people out when they are ready to learn.
-
Hi Colin,
Socratic Irony FTW!
So, it would seem, there are many ways around using cursors and what I described earlier is an example of a ridiculous workaround. The cursor or its equivalent while loop is the way to go there.
It is indeed arguable that an iterative procedure is best left to a different tier, and best coded in a language that is designed for that purpose. A more imperative language, if you will. In the case you cite, and in many others, the addition of an additional tier, while perhaps a better a better solution in theory (and academia), becomes more cumbersome and less attractive. A professor might suggest that you write your backup routine in Visual Basic or some such thing, to be pure, and to save the world one cursor at a time, but if you did that on my watch, I'd be irritated. Not you, but the generic you. You know what I mean.
Because we almost always code in the real world, there is almost no black and white. And there is rarely any "never". I think.
jg
-
I believe in placing as much as possible in the database itself. Considering the rate of change in Visual Studio releases, if you use the CLR integration, there's bound to be something that breaks when .NET 4.0 or whatever comes out. Keeping everything in T-SQL makes for a much safer environment IMHO.
-
that's what I'm hoping for .. ( breaking in CLR ) keep me employed into old age!
As an aside I have to say in my main role of performance tuning cursors are not really something I've come across as being a problem , e.g. I don't very often find them except maybe in third party apps that clients buy - these seem to usually contain every bad use of sql and sql server, but that's another story. So I guess most developers I work with have already been converted to the "cursors are the spawn of the devil" way of thinking?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
WHILE and loop might work much slower than a cursor. Look up
sqlserver-tips.blogspot.com/2006/09/when-eliminataing-cursors-hurts.html
-
I encourage everyone to try a few things here.
I am a huge fan of trying things several ways and simply testing to see what one runs the fastest for the given task. I fell into this habit as an app developer and it natually followed into the DBA realm.
there are 3 methods I try - temp table, table variable, cursor.
I used to use cursors and now almost always use temp table or table variable, sometimes with an index in the case of the temp table, sometimes with a primary key in the case of the table variable. these are always on the identity column I use to keep track of processing. I then loop through that table with a while/counter loop using the max identity.
I just feel that people should just try things instead of blindly using one method or the other.
In my case, the table variable or temp table is faster 90% of the time, but you never know for sure what one will be the quickest unless you try them all. I have samples of all these methods if anyone is interested in code samples
-
Colin, you said
"third party apps that clients buy - these seem to usually contain every bad use of sql and sql server, but that's another story."
If you're starting a new discussion thread, I have lots of really, really bad examples...
-
If I can add something productive to the discussion it would go like this.
If your dealing with a user at the other end of your code in other words someone is invoking your transact SQL code in whatever form it takes (UDF, SPROCS, Triggers, etc.) from an APP, Web site, or whatever then time matters and operations against the RDBMS should reflect every effort to utilize the fastest, least I/O intensive, least logical reads, and most optimal design possible.
If your not dealing with a user at the other end of your code then time does not mean as much. I'm not saying that is a case for using cursors as you can certainly still drive down overall system response time, cpu usage, etc. I'm just saying there is no longer a time argument to consider and other factors should take precedence.
Cursors are not the devil they are also not your saint, they are simply another tool to be aware of, if they where not of some use one assumes Microsoft would have pulled them after release 6.0
Obviously you want developers to utilize and understand all of the SQL language. My belief as to why cursors are/where demonized in the first place goes like this. I was told once that "Given all possible solutions to a problem, Humans will choose what they perceive to be the easiest solution first." I think that principle applies here to cursors! Some junior level developer used them and a senior developer instead of taking time to explain there place and why they are not a good practice in most places in the code simply started the smear campaign.
So if you folks out there want to perpetuate the myth to force junior developers to try a little "harder" to tackle a given problem I'm all for that, but personally I'll still use a cursor when I see a use for them.
If you perceive cursors to be your only option then as the author stated use them to build a prototype as a proof of concept. In going through this process you may discover what Roger Von Oech Ph. D. stated as "A whack on the side of the head" that unlocks your mind set for a new innovation. Maybe it's a redesign of the schema maybe it's utilizing a join you didn't perceive before, but if you don't do the work you will never find out what it is! Once you start to build your new set based version I believe you'll also find it will go much faster as you have already worked out the business rules, hang ups, etc. You will also have something to compare results with in testing to see if one SPROC is returning what the other is and how fast.
I will end by saying that from what I have read and can tell from Edgar F. Codd writings. He created the RDBMS to explicitly work as a "set" based database and works best when used as it was intended. Serial operations add overhead in memory management, the locking manager, thread queues, let alone the fact they generally work with DML on a one row at a time basis where SQL works much better with set based DML.
All that being said, to those that are still unconvinced I offer a challenge. Your reward is educating me and garnering my respect. Not much of a prize I know but I'm open minded to change if it leads to truth and really care about knowing more!
Please return me in a set based SPROC a way of doing what the following sproc does. I wrote this in under 1 hour and have been using it for over a year now.
Thanks
CREATE PROCEDURE [dbo].[SPMassReindex] AS
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE @tablename varchar(255),
@look varchar(1000)
INSERT INTO #fraglist
exec('DBCC SHOWCONTIG WITH TABLERESULTS, FAST')
DECLARE tables CURSOR FOR
SELECT ObjectName
FROM #fraglist
WHERE bestCount < 15000 and
ObjectName not like 'sys%'
-- Open the cursor
OPEN tables
-- Loop through all the tables
FETCH NEXT FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @look = 'DBCC DBREINDEX (''' +rtrim(@tablename) +''', '''', 0)'
EXEC (@look)
FETCH NEXT FROM tables
INTO @tablename
END
CLOSE tables
DEALLOCATE tables
drop table #fraglist
GO
-
Simple. No end user is using this so performance is not absolutely required in this case.
Here 10 seconds and I'm done.
I might also remind you that almost no DBA will try to knock a cursor when used for admin task as the one presented or do this for each table in each db in each server. There's just no way around some sort of looping. However that doesn't mean we should then work on a row by row basis in that inner loop. Set based approach, when possible, will most likely be the fastest to execute, and therefore my first choice.
Also since DBREINDEX cannot accept a list of tables as parameter, your challenge is useless. We would have to rewrite the dbcc function to handle more than one table/index at the same time. Which frankly I wouldn't see the point of doing >> just call the function from 2+ different connections using a global table and split the work and you'll achieve the same thing.
-
I'm at a bit of a loss to understand why it should take "under an hour" to write a peice of code that can be pasted from BOL ??
It still seems that changing a cursor to a while loop is seen as a solution, it isn't becuase it's still a row by row operation and a while is NOT a set based solution.
the dbcc example is no different to my illustration of the sequential backup which was to rubbish the "you never need a cursor/loop" statement , because it's usually fairly easy to find an exception to almost any such statement , I was interested to a solution which didn't involve a loop/cursor for this ( which Jeff kindly provided ) .
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
'Ere Mudluck. It took me a lot less than an hour to modify your code so that it doesn't need, or use, a cursor or a while loop. C'mon 'cursor crew' prove me wrong!
ALTER PROCEDURE [dbo].[SPMassReindex] AS -- Create the table CREATE TABLE #fraglist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) DECLARE @Command VARCHAR(8000) INSERT INTO #fraglist EXEC('DBCC SHOWCONTIG WITH TABLERESULTS, FAST') SELECT * FROM #fraglist IF @@Rowcount>0 BEGIN SELECT @Command=COALESCE(@Command,'')+'DBCC DBREINDEX (''' +RTRIM(Objectname) +''', '''', 0) ' FROM #fraglist WHERE bestCount < 15000 AND ObjectName NOT LIKE 'sys%' EXECUTE( @Command) END DROP TABLE #fraglistBest wishes,
Phil Factor -
And for the final blow : Which one executes the fastest??
-
They both fail to run in zero seconds, but I use a binary collation. Slackers!
I would expect, however, that the difference would be on the order of .001% of the total execution time, provided that there was at least one index that needed work.
-
This is what I found
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.
Phil you code has the dependency of using a 8000 varchar variable to build the string to execute. I can only load about 180 tables before I hit the 8000 limit. I will say thanks for teaching me how to use COALESCE to build a string I had no idea that's cool!
So based on what I can see I guess it would now come down to does a large batch out perform looping through smaller batchs. I dare say that the real bootleneck in this situation is really the I/O to pull down and re-index the tables and not weather there is a looping construct involved or not. If you changed your code to add GO after each re-index so a commit occurs after each re-index then I would say they are about the same impact to the server with maybe and I'm guessing here less then a 1% diff for the actually looping. Which is always the dipping point with this argument. Is the looping slower then the processing in the loop or vs. versa could the processing that is in the loop be (set based)batched in a way that creates a faster operation then a serial one. That is the battle as I see it and I can't say that in all situations that it matters, but is something to be aware of. If I used Phill's SPROC I have to add other code/schema to check to see what I have and have not indexed yet because I have to invoke it multiple times with the cursour I run it once. Is the trade off woth it I think so and those are the questions I think you have to ask yourself.
I will end by saying that most of this sproc came from the DBCC SHOWCONTIG article in BOL (like most good things) I forgot that fact until another poster pointed it out. I'm not trying to take credit for something I didn't do all myself if that matters.
Thanks again Phill and others for getting me involved in thinking about this and trying to offer soemthing postive to the discussion I enjoyed it.
-
Phil - loved the solution - that's what i wanted someone to come up with!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 46 through 60 (of 81 total)
You must be logged in to reply to this topic. Login to reply