April 1, 2008 at 2:12 am
rbarryyoung (4/1/2008)
SQLZ (4/1/2008)
Say I want to run anALTER INDEX ALL ON table_name REORGANIZE;command on all tables that are more than 10% fragmented.I know I can use sys.dm_db_index_physical_stats to get this information instead of using DBCC SHOWCONTIG.
No, you have to use a cursor/loop (of some kind). Executing DDL's/DML's across a restricted range of SQL objects is one of those cases (DB management/operations tasks) that almost always requires Cursors. On the other hand, queries across multiple system objects can almost always avoid them in SQL2005.
Thanks, that's what I thought but it's good to get confirmation.
April 1, 2008 at 5:40 am
SQLZ (4/1/2008)
Say I want to run an ALTER INDEX ALL ON table_name REORGANIZE;command on all tables that are more than 10% fragmented.
I know I can use sys.dm_db_index_physical_stats to get this information instead of using DBCC SHOWCONTIG.
Can we avoid using a cursor in this instance?
Absolutely! You can concatenate the commands using a single SELECT into a single VARCHAR(MAX) and execute the VARCHAR(MAX).
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 5:41 am
rbarryyoung (4/1/2008)
No, you have to use a cursor/loop (of some kind). Executing DDL's/DML's across a restricted range of SQL objects is one of those cases (DB management/operations tasks) that almost always requires Cursors. On the other hand, queries across multiple system objects can almost always avoid them in SQL2005.
Not true in 2k5. You can concatenate the commands using a single SELECT into a single VARCHAR(MAX) and execute the VARCHAR(MAX).
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 5:50 am
Jeff Moden (4/1/2008)
SQLZ (4/1/2008)
Say I want to run an ALTER INDEX ALL ON table_name REORGANIZE;command on all tables that are more than 10% fragmented.
I know I can use sys.dm_db_index_physical_stats to get this information instead of using DBCC SHOWCONTIG.
Can we avoid using a cursor in this instance?
Absolutely! You can concatenate the commands using a single SELECT into a single VARCHAR(MAX) and execute the VARCHAR(MAX).
Cool! So how do we concatenate mulitple rows into a single variable?
April 1, 2008 at 6:59 am
Start here...
http://www.sqlservercentral.com/articles/Test+Data/61572/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 11:20 am
Jeff Moden (4/1/2008)
Start here...
Thank you very much - that's very helpful.
Interestingly, I did a simple test with about 1500 rows (just concatenating names from sys.objects) and I managed to get it to 290ms using the concatenation function and the derived table method you describe in your article. Using a cursor however, I got it working at around 283ms.
Obviously this is a small set of data but cursors aren't all evil :alien: and I think you've got to look at each situation individually. Once you approach 10000 rows you start to notice a difference but for a small data set it seems to make very little difference.
April 1, 2008 at 11:28 am
Another thing to consider is how easy your task is to read/modify later.
For off-hours tasks, I'll take a slow-running cursor that is easy to read over a lightning fast solution that takes forever to get your head arround if you need to modify it later.
You just have to decide what is more important to you given your environment and requirements.
The Redneck DBA
April 1, 2008 at 11:33 am
Karl,
Would you be kind enough to post your cursor code? I'd like to take a peek at it becauase I think I've only written 2 in my life (to demonstrate how slow they are...not for production by any means :hehe:).
And, just to be sure... any cursor that processes a single row at a time is evil 😉 Think about what you just said... for small amounts of data... data ALWAYS grows larger than initially planned.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 12:15 pm
Jeff Moden (4/1/2008)
Karl,Would you be kind enough to post your cursor code? I'd like to take a peek at it becauase I think I've only written 2 in my life (to demonstrate how slow they are...not for production by any means :hehe:).
Jeff,
Sure:
select getdate();
declare @name sysname
declare @names varchar(max)
set @names = ''
declare obj_cursor cursor
for
select SomeCode from TestData
open obj_cursor
fetch next from obj_cursor into @name
while @@fetch_status = 0
begin
select @names = @names + @name +','
fetch next from obj_cursor into @name
end
close obj_cursor
deallocate obj_cursor
select @names
select getdate()
And, just to be sure... any cursor that processes a single row at a time is evil 😉 Think about what you just said... for small amounts of data... data ALWAYS grows larger than initially planned.
You're absolutely right, data ALWAYS grows and I wouldn't normally use a cursor anywhere other than to do simple admin tasks where I know I'm just going over some management views that have tens of rows or hundreds of rows at most.
I wrote a script with several cursors (even with some cursors inside another - and no I'm not the devil's spawn:cool:) which could script about 250 tables (including all constraints, indexes, etc.) in under two seconds. I don't know if you've ever used SQL Server to script 250 tables but it takes slightly longer than a couple of seconds last time I checked. For a script that uses evil RBAR (multiple times) 2 seconds to script all your tables isn't bad in my book.
I like the concatenation function and what that brings and I might modify this script to see if it helps but it's almost not worth the effort - other than for me to learn a bit more.
Like I said, for simple management tasks that I run once in a blue moon or that run on tables that I know are going to remain in the low numbers I feel secure using cursors. But I have seen people use cursors because they only had to deal with 20 rows and 3 years later the cursor is running on 200,000 rows and they're scratching their heads wondering why it's so slow.
April 1, 2008 at 12:32 pm
This will sound a bit like sacrilege on my part... I agree that for some management tasks, they're probably ok... but those types of cursors aren't usually all "RBAR"... normally they're used as a control loop to control multiple reruns of a process that affects many rows for each loop. Still, in 2k5, I'd rather concatenate all of the commands into a VARCHAR(MAX) and let 'er rip.
Hey! Thanks for posting the code! I'll take a look!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 12:53 pm
I'm with Jeff on this one. Since I've been into SQL Server, I have written 2 cursors. One was for analysis purposes to gather info. on all databases attached to a SQL instance at a client site. The second cursor I wrote when I created our standards and guidelines just so I could prove a point on how inefficient they are.
Sure, there are some tasks that you can write a 'harmless' cursor for because the scope is so small, but to me, the impact is still great. Let me explain. It's about habits. If you frequently use cursors as just another tool in your toolbox, you are not thinking set based. Just pretend like they don't exist and you won't have to worry about asking the question, 'how can I do this without a cursor?' because a cursor will not even be a choice!
Take away cursors as your plan B and you'll start to see problems and solutions set based by default. The more you force yourself to look at a solution from a set based perspecitve, the easier it gets to find the set based solution. You may bang your head against the wall for a while, but over time, the habit of using set based soltions will make finding the correct, set based solution easy to find. In fact, sometimes they'll just jump right out at you. 🙂
Just my $.02
April 1, 2008 at 1:25 pm
John Rowan (4/1/2008)
I'm with Jeff on this one. Since I've been into SQL Server, I have written 2 cursors. One was for analysis purposes to gather info. on all databases attached to a SQL instance at a client site. The second cursor I wrote when I created our standards and guidelines just so I could prove a point on how inefficient they are.Sure, there are some tasks that you can write a 'harmless' cursor for because the scope is so small, but to me, the impact is still great. Let me explain. It's about habits. If you frequently use cursors as just another tool in your toolbox, you are not thinking set based. Just pretend like they don't exist and you won't have to worry about asking the question, 'how can I do this without a cursor?' because a cursor will not even be a choice!
Take away cursors as your plan B and you'll start to see problems and solutions set based by default. The more you force yourself to look at a solution from a set based perspecitve, the easier it gets to find the set based solution. You may bang your head against the wall for a while, but over time, the habit of using set based soltions will make finding the correct, set based solution easy to find. In fact, sometimes they'll just jump right out at you. 🙂
Just my $.02
Hey John, I am in agreement. Particularly about it becoming a habit. That said, I don't think that just because a cursor is the wrong thing to use 99% of the time that we should completely ignore it because we've forced ourselves into the habit of never using a cursor at all costs.
There is a time and place for cursors. It may only be 1% of the time. The trick is in knowing when to use them. Try scripting all of the tables (including their associated constraints/indexes) in your database without a cursor, using only set-based T-SQL. I'm honestly not sure it's possible and if it is possible I don't think it would beat my, 2 second, cursor-based script by much.
When a developer comes to me and asks me about cursors I'll say they're evil, you're not doing that on my database! I'm the DBA though and "I know what I'm doing" so it's ok for me to use them. It's a question of "do as I say, not as I do" where the devs are concerned 😉
April 1, 2008 at 1:35 pm
Heh... Karl... no wonder that it appears that your cursor beats the function... your cursor doesn't do the same thing/produce the same row set... I'm working on it...
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 2:01 pm
SQLZ (4/1/2008)
The trick is in knowing when to use them.
That is absolutely correct! The problem is that most people simply give up too soon!
When a developer comes to me and asks me about cursors I'll say they're evil, you're not doing that on my database! I'm the DBA though and "I know what I'm doing" so it's ok for me to use them. It's a question of "do as I say, not as I do" where the devs are concerned 😉
Heh... you sure? First time a developer catches you, your credibility as a DBA will make flushing sounds. 😛
Try scripting all of the tables (including their associated constraints/indexes) in your database without a cursor, using only set-based T-SQL. I'm honestly not sure it's possible and if it is possible I don't think it would beat my, 2 second, cursor-based script by much.
Ah ha! Finally! A challenge! If you would, post the resulting table creation script so I can duplicate what you have... the race is on! :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 2:06 pm
SQLZ (4/1/2008)
Hey John, I am in agreement. Particularly about it becoming a habit. That said, I don't think that just because a cursor is the wrong thing to use 99% of the time that we should completely ignore it because we've forced ourselves into the habit of never using a cursor at all costs.There is a time and place for cursors. It may only be 1% of the time. The trick is in knowing when to use them. Try scripting all of the tables (including their associated constraints/indexes) in your database without a cursor, using only set-based T-SQL. I'm honestly not sure it's possible and if it is possible I don't think it would beat my, 2 second, cursor-based script by much.
When a developer comes to me and asks me about cursors I'll say they're evil, you're not doing that on my database! I'm the DBA though and "I know what I'm doing" so it's ok for me to use them. It's a question of "do as I say, not as I do" where the devs are concerned 😉
It's pretty clear at this point that the strong anti-cursor bias here is because you guys are all DBAs. I believe that for administrative/managerial tasks I agree with you wholeheartedly.
What you guys have to remember - and the above quote is perfect - is that the only reason you are managing the database is so that someone can use the data. In no way do I mean this to imply that the administrative/managerial tasks are not important. However, administration is "set-based" while using data is often record-based. There are countless examples:
1. A piece of software must gather all new submissions (a record set) and step through the set (a cursor) presenting some data from the record (e.g. a suspect's photograph) to a human reviewer for review.
2. A piece of software must find all records sharing some ID ( a record set) and then step through those records (a cursor) performing the instructions encoded in each record (e.g. probing remote sensors for current readings) and waiting for a return value before proceeding.
etc.
These all generalize to the following: A piece of software must perform an external process on each record in a record set.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply