December 27, 2007 at 12:15 pm
i have a job which is failing
my lead wants me to Remove the cursors from the job and modify it
any suggestion would be of great help please
--
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT database_name FROM WHERE status='y'
AND database_name NOT IN ('master','model','msdb','tempdb')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
thanks
December 27, 2007 at 12:34 pm
My first inclination about this job is to abandon it... no, I didn't say rewrite it... I do mean "abandon it". This will change the Fill Factor on all tables and that's an absolute Bozo-no-no. There are (should be) a large number of static definition tables that never/rarely change that should have a fill factor of 100 just for performance reasons. Also, any table that has a Primary Key on an IDENTITY column should also have a Fill Factor of 100 on that Primary Key because there's no chance for interceding rows, so no interference on INSERTs/UPDATEs by the 100 fill factor but will increase the speed of SELECT's and decrease the storage requirements on the IDENTITY Primary Key... clustered or not.
The basis of the code is a really bad idea...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 27, 2007 at 1:13 pm
I can't think of a way to do this without loops of some sort, either cursors or a while loop. Since changing the existing nested cursors to nested while loops, would just be an exercise in writing code for the sake of writing code, with no real benefit, I can't suggest anything on this.
On the other hand, the reason this exact task isn't something that's easy to set up in a maintenance plan (for example), is because what this code does is pretty much a bad idea in the first place, as already pointed out in another comment.
What I would suggest is find out why this code is in use in the first place, and then maybe we can help with a solution to the underlying problem. It exists to solve something from someone's point of view - and that something probably has a better, standard solution.
- 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
December 28, 2007 at 2:56 am
sorry Jeff I have to disagree, even if the clustered index is on an indentity column then the clustered index is actually the entire table, regardless of the index being defined on an int or bigint, so if the next couple of columns are varchars and the values( length of these) change during editing you may well get page splits if you cannot perform an inplace update, in this case a fill factor may help avoid splits.
That said I find most applications of fill factors and padding are missguided at best - I usually challenge implementers of such code to prove and justify the action - they usually can't, which isn't too uncommon for many diverse changes/actions which affect sql server ( how urban myths arise ? )
As to the original post you could change the cursor to a while ( which is just a cursor in disguise ) but the way sql handles transactions is different for a while and a cursor so you may find yourself digging yourself into a hole.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 28, 2007 at 11:48 am
sorry Jeff I have to disagree, even if the clustered index is on an indentity column then the clustered index is actually the entire table, regardless of the index being defined on an int or bigint, so if the next couple of columns are varchars and the values( length of these) change during editing you may well get page splits if you cannot perform an inplace update, in this case a fill factor may help avoid splits.
Crud... you're right, Colin... I forgot about the eventuality of Updates like this because I'm so bloody entrenched in ETL processes where the data is imported and never changes... thank you for the correction.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2007 at 8:09 am
no worries - I find myself often trying to explain why a fill factor of 80% is such a waste of space on etl import tables, or even read only filegroups!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 5, 2010 at 3:38 am
Hello Sir
I just wanna know that what is cursor
is it datatype or object and how
and what is use of cursor when we have its alternate
and how a cursor can give us maltiple value like
empId salary
101 10000
105 5000
July 5, 2010 at 9:13 am
vedpsoni (7/5/2010)
Hello SirI just wanna know that what is cursor
is it datatype or object and how
and what is use of cursor when we have its alternate
and how a cursor can give us maltiple value like
empId salary
101 10000
105 5000
My recommendation is to lookup "cursors [SQL Server]" in Books Online (the help system that comes with SQL Server) because CURSORs are a big subject. In the meantime, here's a simple cursor that reads a couple of values from a table and displays them. If you're in the GRID mode, it will end with an error. I've commented the code so you can see what each piece does.
USE AdventureWorks;
GO
--===== Declare some cursor related variables
DECLARE @EmployeeID INT,
@Title NVARCHAR(50)
--===== Declare the cursor using a SELECT
DECLARE Employee_Cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT EmployeeID, Title
FROM HumanResources.Employee;
--===== Open the cursor to begin using it.
-- This is where a static cursor gets loaded
-- into a temp table
OPEN Employee_Cursor;
--===== Start an infinite loop. We'll break out later...
WHILE 1 = 1
BEGIN
--===== Read a row from the cursor
FETCH NEXT FROM Employee_Cursor
INTO @EmployeeID, @Title;
--===== If the read above returns NO row, exit the loop
-- because we're done
IF @@FETCH_STATUS <> 0 BREAK;
--===== If we're still here, then there was a row to be read.
-- We can now process that row.
SELECT @EmployeeID, @Title;
--===== This marks the end of the While Loop which automatically continues
-- up to here until we hit the "BREAK" in the code above.
END;
--======== Release any locks held open by the cursor and then drop the
-- cursor structure.
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
So far as what to use a CURSOR for goes, the answer is almost always that you shouldn't use a CURSOR. I'll also tell you that writing a Temp Table or Table Variable to step through using a While Loop is nothing more than a poor man's CURSOR and should also be avoided 99.99% of the time.
The only time I'll condone (never mind allow in my shop) is when you're trying to do something to all tables in a database and other object control related things. Even then, you can get away with concatenated commands rather than using a CURSOR.
Don't be fooled by recursive CTEs or the use of things like sp_MSForEachTable... recursive CTEs are generally a form of hidden RBAR and sp_MSForEachTable is nothing more than a huge, very ugly CURSOR in the background.
what is use of cursor when we have its alternate
The answer is, it allows people who don't know how to do high performance, set-based code to still get to their data and do some processing. CURSORs were originally meant to make it a bit easier to do something to, say, all tables in a database but others have used them to overcome their lack of set-based knowledge. Generally speaking, cursors should be avoided no matter what the cost because they are usually terrible for performance and resource usage. I've never put a CURSOR into production code and about the only time I use a While Loop is to step through file names during T-SQL imports of files. Even that isn't RBAR processing... it's a control loop to load thousands/millions of rows for each file in a set-based manner.
Just to summarize and emphasize... if you use a CURSOR, While Loop, or any form of recursion to affect just one row at a time (RBAR), there's a very, very, high probability that you're doing it the wrong way whether you can think of a set-based method or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2010 at 8:55 am
Jeff Moden (7/5/2010)
... In the meantime, here's a simple cursor that reads a couple of values from a table and displays them. If you're in the GRID mode, it will end with an error. I've commented the code so you can see what each piece does.
USE AdventureWorks;GO
--===== Declare some cursor related variables
DECLARE @EmployeeID INT,
@Title NVARCHAR(50)
--===== Declare the cursor using a SELECT
DECLARE Employee_Cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT EmployeeID, Title
FROM HumanResources.Employee;
--===== Open the cursor to begin using it.
-- This is where a static cursor gets loaded
-- into a temp table
OPEN Employee_Cursor;
--===== Start an infinite loop. We'll break out later...
WHILE 1 = 1
BEGIN
why not
WHILE (@@FETCH_STATUS = 0)
July 6, 2010 at 9:50 am
I personally hate cursors and try not to use them if at all possible. In keeping with our TSQL roots I submit:
DECLARE @Database VARCHAR(255)
DECLARE @DBList TABLE (DBName varchar(255), Processed CHAR(1))
-- Populate the in-memory table @DBList with all of the database names
insert @DBList
Select [name] , 'N' from master..sysdatabases
where [name] NOT IN( 'model','master','tempdb','msdb')
--select * from @DBList -- DEBUG: Run this to prove population was successful
-- Grab the first DB name from our in-memory table
While EXISTS (Select top 1 DBName from @DBList where Processed='N' order by DBName)
BEGIN
-- Get the DB Name into the @Database variable
Set @Database = (Select top 1 DBName from @DBList where Processed='N' order by DBName)
-- Do whatever awesome stuff with the database...
print @Database
-- Update the Processed flag to skip this DB on the next WHILE pass. Ever-decreasing list
Update @DBList set Processed='Y' where DBName = @Database
END
Efficient? Nope. Easy to write? Yep! Understandable for the DBA that replaces you? Absolutely!
Hope this helps 😉
July 6, 2010 at 10:15 am
HoustonFirefox (7/6/2010)
I personally hate cursors and try not to use them if at all possible. In keeping with our TSQL roots I submit:
DECLARE @Database VARCHAR(255)DECLARE @DBList TABLE (DBName varchar(255), Processed CHAR(1))
-- Populate the in-memory table @DBList with all of the database names
insert @DBList
Select [name] , 'N' from master..sysdatabases
where [name] NOT IN( 'model','master','tempdb','msdb')
--select * from @DBList -- DEBUG: Run this to prove population was successful
-- Grab the first DB name from our in-memory table
While EXISTS (Select top 1 DBName from @DBList where Processed='N' order by DBName)
BEGIN
-- Get the DB Name into the @Database variable
Set @Database = (Select top 1 DBName from @DBList where Processed='N' order by DBName)
-- Do whatever awesome stuff with the database...
print @Database
-- Update the Processed flag to skip this DB on the next WHILE pass. Ever-decreasing list
Update @DBList set Processed='Y' where DBName = @Database
END
Efficient? Nope. Easy to write? Yep! Understandable for the DBA that replaces you? Absolutely!
Hope this helps 😉
This while loop can be worse than a cursor. What you should be trying to avoid is not cursors, but all looping mechanisms - you need to avoid the WHILE statement compeletely.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 6, 2010 at 11:03 am
j-1064772 (7/6/2010)
why not
WHILE (@@FETCH_STATUS = 0)
You could certainly do it that way... I just like to avoid the extra FETCH that requires.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2010 at 7:57 am
Cursors are appropriate for some classes of problems - and metadata operations can be one of them. Use the correct tool for the job.
I am with Jeff that you should not make every table have the same fill factor (although making them all something other than 0 is often an improvement). Best is to analyze fragmentation rates and adjust index fill factors appropriately based on that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2011 at 10:30 am
This is admittedly a niggling question, but in Jeff's sample cursor, he used:
DECLARE Employee_Cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
If you want the cursor to be FORWARD_ONLY READ_ONLY, why wouldn't you just declare it FAST_FORWARD? What are the intrinsic differences that I'm missing?
Thanks,
~ J
November 4, 2011 at 11:48 pm
You could, indeed. But way back when, someone suggested that FAST FORWARD isn't as fast as naming the options separately. I did a test back then and they were right. I don't know if it's changed with SPs, revision changes, etc, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply