cursors
-
hi guys what are cursors? why are they so bad? and is there a way to check on what cursors i have in my sql server databases?
thanks!
jessica
-
Because cursors indicate a row-by-row (if this row contains ... then I'm not interested) logic whilst sql server is optimized for set-based logic (give me all that have the value ...).
-
The real reason behind this is that most of the time 95%+, then cursor based approach is gonna be slower, sometimes exponentially slower (if cursor inside a cursor) than the equivalent set based query.
This statement is often made to "scare"/force someone into trying to find the appropriate set based solution. This is so because newbies to sql server try to emulate what they have learnt to do in vb or c# and process row by row in a while. While there's theorically nothing wrong with that approach, it's one of the worst ways to work in sql server. So that's why we generally tell someone to avoid it. However there are quite a few exceptions, especially when dealing in administration tasks. Such as, for each table, in each database in each server, do this process...
There's just no way to avoid a cursor (well any row by row construct) in this case.
-
As to the question about checking, you could write a cursor that would check all databases on the server for cursors :-).
Problem is, that some cursors are "hidden" to such check, because they are part of the application which uses the database - not part of the database itself - so you don't find all. To check the database, you'd need to look into stored procedures, user defined functions and triggers. These are all stored in syscomments table, in column text:
SELECT so.[name], so.xtype, sc.[text]
FROM syscomments sc
JOIN sysobjects so ON so.[id]=sc.[id]
WHERE sc.[text] LIKE '%cursor%'
Read something about the system tables and find out, what more can you get from them... this is just a basic outline. And one warning: this is not guaranteed to bring 100% of occurences, since longer procedure can be stored in several rows of syscomments table, with part of the searched word in one row and rest in another. But in your case, it should work well.
-
thank you all of you!!!!
-
not exponentially slower, only power law slower.
---------------------------------------
elsasoft.org -
One of the other problems with Cursors AND While-Loops is the number of resources (mostly cpu time) they use when compared to properly written setbased code... the following example code is written to use and compare a Cursor, a While-Loop, and Setbased code. The code doesn't really do anything useful... All each "module" does is measure what it takes to read a given number of rows into a variable using each of the 3 methods. Here's the output from a run on my home machine (SQL Server 2000 sp3a Developer Edition, 1.8Ghz Pentium, 2Gig Ram, 80 gig IDE HD).
(100000 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
TestType TotalDurMS TotalCPU TotalIO
------------- ----------- ----------- --------------------
Cursor totals 2733 2344 10
(1 row(s) affected)
(100000 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
TestType TotalDurMS TotalCPU TotalIO
----------------- ----------- ----------- --------------------
While Loop totals 2326 1968 10
(1 row(s) affected)
(100000 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
TestType TotalDurMS TotalCPU TotalIO
---------------- ----------- ----------- --------------------
Set Based totals 93 78 10
(1 row(s) affected)
...and, here's the code...
--===== Declare local variables
DECLARE @TestSizeRows INT
SET @TestSizeRows = 100000
DECLARE @StartTime DATETIME
DECLARE @CPU INT
DECLARE @io INT
DECLARE @Bitbucket INT
--\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
--===== Create the CURSOR test table in a database we know everyone has
USE TEMPDB
IF OBJECT_ID('dbo.CursorTest') IS NOT NULL
DROP TABLE CursorTest
SET ROWCOUNT @TestSizeRows
SELECT IDENTITY(INT,1,1) AS SomeNumber
INTO dbo.CursorTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
SET ROWCOUNT 0
--===== Every table should have a primary key
ALTER TABLE dbo.CursorTest
ADD CONSTRAINT PK_CursorTest_SomeNumber
PRIMARY KEY CLUSTERED (SomeNumber)
--=============================================================================
-- Cursor solution for loading a variable with 100,000 numbers.
-- Previous values in the variable are simply discarded to prevent display
-- or update IO from tainting the times.
--=============================================================================
--===== Clear cache and reset buffers
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
--===== Get starting resource values
SELECT @StartTime = GETDATE(),
@CPU = SUM(CPU),
@io = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
--===== Run the cursor solution
DECLARE TestCursor CURSOR FAST_FORWARD
FOR
SELECT SomeNumber
FROM CursorTest
ORDER BY SomeNumber
OPEN TestCursor
-- Perform the required first fetch
FETCH NEXT FROM TestCursor INTO @Bitbucket
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM TestCursor INTO @Bitbucket
END
-- Perform the required "housecleaning" to prevent "memory leaks", etc.
CLOSE TestCursor
DEALLOCATE TestCursor
--===== Get ending resource values and calc totals
SELECT 'Cursor totals' AS TestType,
DATEDIFF(ms,@StartTime,GETDATE()) AS TotalDurMS,
SUM(CPU) - @CPU AS TotalCPU,
SUM(Physical_IO) - @io AS TotalIO
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
--\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
--===== Create the WHILE LOOP Test table in a database we know everyone has
USE TEMPDB
IF OBJECT_ID''dbo.WhileTes'') IS NOT NULL
DROP TABLE WhileTest
SET ROWCOUNT @TestSizeRows
SELECT IDENTITY(INT,1,1) AS SomeNumber
INTO dbo.WhileTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
SET ROWCOUNT 0
--===== Every table should have a primary key
ALTER TABLE dbo.WhileTest
ADD CONSTRAINT PK_WhileTest_SomeNumber
PRIMARY KEY CLUSTERED (SomeNumber)
--=============================================================================
-- WHILE loop solution for loading a variable with 100,000 numbers.
-- Previous values in the variable are simply discarded to prevent display
-- or update IO from tainting the times.
--=============================================================================
--===== Clear cache and reset buffers
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
--===== Declare Local variables
DECLARE @Counter INT
SET @Counter = 1
--===== Get starting resource values
SELECT @StartTime = GETDATE(),
@CPU = SUM(CPU),
@io = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
WHILE @Counter <= @TestSizeRows
BEGIN
SELECT @Bitbucket = SomeNumber
FROM WhileTest
WHERE SomeNumber = @Counter
SET @Counter = @Counter + 1
END
--===== Get ending resource values and calc totals
SELECT''While Loop total'' AS TestType,
DATEDIFF(ms,@StartTime,GETDATE()) AS TotalDurMS,
SUM(CPU) - @CPU AS TotalCPU,
SUM(Physical_IO) - @io AS TotalIO
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
--\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
--===== Create the SET BASED test table in a database we know everyone has
USE TEMPDB
IF OBJECT_I(''dbo.SetBaseTet'') IS NOT NULL
DROP TABLE SetBaseTest
SET ROWCOUNT @TestSizeRows
SELECT IDENTITY(INT,1,1) AS SomeNumber
INTO dbo.SetBaseTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
SET ROWCOUNT 0
--===== Every table should have a primary key
ALTER TABLE dbo.SetBaseTest
ADD CONSTRAINT PK_SetBaseTest_SomeNumber
PRIMARY KEY CLUSTERED (SomeNumber)
--=============================================================================
-- SetBased solution for loading a variable with 100,000 numbers.
-- Previous values in the variable are simply discarded to prevent display
-- or update IO from tainting the times.
--=============================================================================
--===== Clear cache and reset buffers
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
--===== Get starting resource values
SELECT @StartTime = GETDATE(),
@CPU = SUM(CPU),
@io = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
SELECT @Bitbucket = SomeNumber
FROM SetBaseTest
ORDER BY SomeNumber
--===== Get ending resource values and calc totals
SELEC ''Set Based totas'' AS TestType,
DATEDIFF(ms,@StartTime,GETDATE()) AS TotalDurMS,
SUM(CPU) - @CPU AS TotalCPU,
SUM(Physical_IO) - @io AS TotalIO
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
GO
--====== End of test... clenaup the test tables
IF OBJECT_I(''dbo.CursorTet'') IS NOT NULL
DROP TABLE CursorTest
IF OBJECT_I(''dbo.WhileTet'') IS NOT NULL
DROP TABLE WhileTest
IF OBJECT_I(''dbo.SetBaseTet'') IS NOT NULL
DROP TABLE SetBaseTest
The "key" though, is writting "good" set based code. First, "Set Based" doen''t necessarily mean "all in one SELECT". The other thing you have to watch for is triangular joins (half a cartesion join... tell tale sign is "<" or ">" in the join) and some forms of correlated subqueries.
For example, the following (running count example) looks like set based code, but t''s really RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") on steriods...
SELECT p2.id, COUNT(p1.id)
FROM PERSONS p1,PERSONS p2
WHERE p1.id <= p2.id
GROUP BY p2.id
...takes days to process a lousy million rows... even a cursor is faster than that. Thee''s a dozen different ways to do this without RBAR and without a cursor or while loop if you look on this forum for them.<
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanx for the good work.
IIRC, you were supposed to write an article about this. How is that comming along?
-
Its a bit difficult... I have a hard time thinking in a non-set based fashion when trying to write the cursor examples
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'm sur eyou can find someone who can do that part for you
. -
Heh... ya... hence the need for the article. Thanks for the reminder
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply