How do the cursors work?

Question

How do the cursors work?

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
October 25, 2006 at 9:07 am

#121186

i know that is very bad to use cursors , only is recommendable for a few registers,
but why ? only i know because the cursors work the registers one by one, but exists another reason for not use them?? the cursors create a temporal table and lock the registers????
or use too cache memory??? what do the cursors do to work the datas?
i know too that exists a cursor called fast_foward, is recommendable ? the fast foward cursor is better than use a variable table??? thanks for you helpings sorry my bad english

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 1

Depending on how you declare a cursor, they can cause undesired locking, the can strain your tempdb, I have known cursors to crash, but the biggest reason to avoid cursors is because they are very inefficient. SQL server is very fast and efficient when dealing with large amounts of data using SELECT/UPDATE/DELETE (Set based) but very inefficient dealing with large amounts of data one row (register) at a time (iterative).

Here is a simple example of the performance difference. Run this and compare the timestamps.

SELECT  (i6.number * 100000 + i5.number * 10000 + i4.number * 1000 + i3.number * 100 + i2.number * 10 + i1.number) AS ident,
        'Hello World' AS value
INTO    #table
FROM    (
          SELECT    0 AS [number]
          UNION ALL
          SELECT    1 AS [number]
          UNION ALL
          SELECT    2 AS [number]
          UNION ALL
          SELECT    3 AS [number]
          UNION ALL
          SELECT    4 AS [number]
          UNION ALL
          SELECT    5 AS [number]
          UNION ALL
          SELECT    6 AS [number]
          UNION ALL
          SELECT    7 AS [number]
          UNION ALL
          SELECT    8 AS [number]
          UNION ALL
          SELECT    9 AS [number]
        ) AS i1 
        CROSS JOIN (
                     SELECT 0 AS [number]
                     UNION ALL
                     SELECT 1 AS [number]
                     UNION ALL
                     SELECT 2 AS [number]
                     UNION ALL
                     SELECT 3 AS [number]
                     UNION ALL
                     SELECT 4 AS [number]
                     UNION ALL
                     SELECT 5 AS [number]
                     UNION ALL
                     SELECT 6 AS [number]
                     UNION ALL
                     SELECT 7 AS [number]
                     UNION ALL
                     SELECT 8 AS [number]
                     UNION ALL
                     SELECT 9 AS [number]
                   ) AS i2 
        CROSS JOIN (
                     SELECT 0 AS [number]
                     UNION ALL
                     SELECT 1 AS [number]
                     UNION ALL
                     SELECT 2 AS [number]
                     UNION ALL
                     SELECT 3 AS [number]
                     UNION ALL
                     SELECT 4 AS [number]
                     UNION ALL
                     SELECT 5 AS [number]
                     UNION ALL
                     SELECT 6 AS [number]
                     UNION ALL
                     SELECT 7 AS [number]
                     UNION ALL
                     SELECT 8 AS [number]
                     UNION ALL
                     SELECT 9 AS [number]
                   ) AS i3 
        CROSS JOIN (
                     SELECT 0 AS [number]
                     UNION ALL
                     SELECT 1 AS [number]
                     UNION ALL
                     SELECT 2 AS [number]
                     UNION ALL
                     SELECT 3 AS [number]
                     UNION ALL
                     SELECT 4 AS [number]
                     UNION ALL
                     SELECT 5 AS [number]
                     UNION ALL
                     SELECT 6 AS [number]
                     UNION ALL
                     SELECT 7 AS [number]
                     UNION ALL
                     SELECT 8 AS [number]
                     UNION ALL
                     SELECT 9 AS [number]
                   ) AS i4 
        CROSS JOIN (
                     SELECT 0 AS [number]
                     UNION ALL
                     SELECT 1 AS [number]
                     UNION ALL
                     SELECT 2 AS [number]
                     UNION ALL
                     SELECT 3 AS [number]
                     UNION ALL
                     SELECT 4 AS [number]
                     UNION ALL
                     SELECT 5 AS [number]
                     UNION ALL
                     SELECT 6 AS [number]
                     UNION ALL
                     SELECT 7 AS [number]
                     UNION ALL
                     SELECT 8 AS [number]
                     UNION ALL
                     SELECT 9 AS [number]
                   ) AS i5
        CROSS JOIN (
                     SELECT 0 AS [number]
                     UNION ALL
                     SELECT 1 AS [number]
                     UNION ALL
                     SELECT 2 AS [number]
                     UNION ALL
                     SELECT 3 AS [number]
                     UNION ALL
                     SELECT 4 AS [number]
                     UNION ALL
                     SELECT 5 AS [number]
                     UNION ALL
                     SELECT 6 AS [number]
                     UNION ALL
                     SELECT 7 AS [number]
                     UNION ALL
                     SELECT 8 AS [number]
                     UNION ALL
                     SELECT 9 AS [number]
                   ) AS i6
ORDER BY
        [ident]
 


CREATE CLUSTERED INDEX IXC__Table__Ident ON #table (Ident )

SELECT 'Set Based approach using UPDATE Start', GETDATE()
UPDATE  #table
SET     Value = REPLACE(value, 'World', 'Earth')
SELECT 'Set Based approach using UPDATE end', GETDATE() 


DECLARE @value VARCHAR(30)

DECLARE verySlow CURSOR LOCAL OPTIMISTIC FOR SELECT Value
                                             FROM   #table FOR UPDATE OF Value

OPEN verySlow

FETCH NEXT FROM verySlow INTO @value
 
SELECT 'Iterative approach using a cursor start', GETDATE()
WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE  #table
        SET     Value = REPLACE(@Value, 'Earth', 'World')
        WHERE CURRENT OF verySlow

        FETCH NEXT FROM verySlow INTO @Value 
    END

SELECT 'Iterative approach using a cursor end', GETDATE()

DROP TABLE #table

My advice is not to use cursors. There are alternatives and, in most cases, you should be able to rewrite your query to avoid an ‘iterative’ solution altogether.

I hope this is clear, let me know if you have any more questions.

SQL guy and Houston Magician

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Spot on, Robert!

--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)

Chris Foster-356495 SSC Veteran Points: 224 More actions · Answer 3

Cursors aren't "very bad". They're one of many techniques you could use to do a job. If a select/update/delete command can be used instead, do so. But there are cases where a cursor is necessary. In these cases it is meaningless to compare the two techniques. It's similar to the stored procs vs. dynamic sql - use whatever is appropriate to do the job.

Michael G Ten Centuries Points: 1323 More actions · Answer 4

Couldn't have explained it better myself.

From past experience cursors are okay if you're planning to get results in under a few hundred or thousands. Once you start looking at more results use a join.

Cursors can come in handy when stepping forwards and backwards through a result, but that handiness is overruled by slowness.

Michael Gilchrist
Database Specialist
There are 10 types of people in the world, those who understand binary and those that don't. 😀

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 5

very interesting , thank you for your tips, i will try to do my operation using variable table, but now i have the next question, i know that variable table cant build some index, well then what is better for a big amount of registers , variable table or temporal table?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

>>But there are cases where a cursor is necessary.

I would agree... BUT, the problem is most people don't really know where that boundary is... a lot of folks figure that if THEY can't figure out how to do a set-based solution, then they should give up and use a cursor instead of getting the necessary help to make a setbased solution. And they get REAL lazy about the whole set based thing if they know they can use a cursor.

They'll also try the old trick of creating a temp table and stepping though that in a RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") fashion... not much different than a cursor and definitely NOT set based.

Even for GUI code... return a result set... let the app "step" through the rows that are returned...

Don't use cursors in SQL Server... if you really, really like cursors or you can't get the setbased thing down, you might want use Oracle instead... they kinda cater to people who don't know set based programming (for example, Oracle triggers require single row ops and you MUST use an Oracle Reference Cursor to return a result to a GUI).

--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)

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 7

For many records (registers), a temporary table is recommended. The reason for this is twofold. First, as you pointed out, you can use indexes*. Second, when your table variable gets too large, SQL server implicitly converts it into a temporary table anyway.

My rule of thumb is to use a tempory table when I expect the rows in the table to range into the thousands plus (perhaps others can share there thoughts on this)

What are you working on? You may not need a temporary table at all. I think we'd all be interested in having a look.

* (this may no longer be the case but...) While you can create indexes on a temporary table, I have personally found that indexes created outside of the CREATE TABLE statement are not used. From what I have read, this is because SQL server already creates the query plan before it knows about the indexes. I have found a few techniques to sidestep this but I believe SQL server can get it's knickers in a twist when you mix DDL and DML in a proc. Any thoughts, anyone?

SQL guy and Houston Magician

MattieNH SSCertifiable Points: 7463 More actions · Answer 8

very interesting , thank you for your tips, i will try to do my operation using variable table, but now i have the next question, i know that variable table cant build some index, well then what is better for a big amount of registers , variable table or temporal table?

Temporary tables. Unless I'm positive that the number of rows in a table variable will remain very small, I don't even bother using them anymore. I've cut processing time by 90% by switching from a table variable to a temporary table.

Mattie

John Rowan SSC Guru Points: 56440 More actions · Answer 9

Just because you've decided not to go the cursor route does not necessarily mean that you need to use a table variable or temp table. Odds are, there's a set based solution that does not require temporary storage of data.

Also, keep in mind that you can define a primary key on a table variable and get the benefits of a clustered index!

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 10

interesting i dont know that sql server implicitly converts it into a temporary table anyway when the table variable gets too large, but what do you call too large? millons of registers? or when you cache memory server is full or low ?? thanks

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 11

Here is a good article on table variables and temporary tables. From reading this article, it seems I may be wrong about large table variables being converted into temporary tables. Have a look and I hope it answers some of your questions!

http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html

SQL guy and Houston Magician

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

I strongly recommend AGAINST using table variables for anything more than a dozen or so rows because they cannot be made to use statistics... both table variables and temp tables start out in memory and spill into TempDB if they get too big...

Reserve the use of table variables for functions that need an arrary like structure to work with. If functions allowed Temp Tables, I'd never even use a table variable.

Just so you don't think I'm totally off my rocker... read "Q3" amd "Q4" of the following link very carefully...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

--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)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

Correct... large table variables are NOT converted to temp tables...

I strongly recommend AGAINST using table variables for anything more than a dozen or so rows because they cannot be made to use statistics... both table variables and temp tables start out in memory and spill into TempDB if they get too big...

Reserve the use of table variables for functions that need an arrary like structure to work with. If functions allowed Temp Tables, I'd never even use a table variable.

Just so you don't think I'm totally off my rocker... read "Q3" amd "Q4" of the following link very carefully...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

--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)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

Matti and John... you guys are "spot on" about temp tables. John, I absolutely agree... lot's of times, a good set based solution will require NO temporal structure whatsoever.

--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)