How do the cursors work?
-
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
-
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
-
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)
Intro to Tally Tables and Functions -
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.
-
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. 😀 -
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?
-
>>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)
Intro to Tally Tables and Functions -
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
-
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
-
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!
-
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
-
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
-
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)
Intro to Tally Tables and Functions -
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)
Intro to Tally Tables and Functions -
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)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply