Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cursor Expand / Collapse
Author
Message
Posted Thursday, December 27, 2007 12:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:33 AM
Points: 49, Visits: 399
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
Post #436805
Posted Thursday, December 27, 2007 12:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #436814
Posted Thursday, December 27, 2007 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #436823
Posted Friday, December 28, 2007 2:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #436958
Posted Friday, December 28, 2007 11:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437168
Posted Saturday, December 29, 2007 8:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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!

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #437327
Posted Monday, July 5, 2010 3:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 15, 2012 5:55 AM
Points: 197, Visits: 61
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
Post #947439
Posted Monday, July 5, 2010 9:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
vedpsoni (7/5/2010)
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


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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #947556
Posted Tuesday, July 6, 2010 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:48 AM
Points: 106, Visits: 510
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)


Post #947959
Posted Tuesday, July 6, 2010 9:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 9:34 AM
Points: 49, Visits: 106
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
Post #947995
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse