April 12, 2009 at 12:20 am
I read many post suggesting to avoid cursor as it degrade the performance. But I am confised why cursor is slower. How are cursor and table variable handles data set operation.
April 12, 2009 at 2:10 am
Wish (4/12/2009)
I read many post suggesting to avoid cursor as it degrade the performance. But I am confised why cursor is slower.
Because cursors are iterative, row-by row operations. SQL is optimised to work on set of data. It will performa an operation on a million rows far faster than it will perform the same operation 1 million times on a single row.
How are cursor and table variable handles data set operation.
Not sure what you're asking. Can you explain more please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2009 at 9:59 pm
Unlike ORacle, SQL is not optimized for cursors. It's optimized for set based operations, and as Gail mentioned, it is far slowed working with cursors.
April 13, 2009 at 10:02 am
Steve Jones - Editor (4/12/2009)
Unlike ORacle, SQL is not optimized for cursors. It's optimized for set based operations, and as Gail mentioned, it is far slowed working with cursors.
Steve, Yo have knowledge about Oracle..i think you can start OracleCentral :).
April 13, 2009 at 10:05 am
Ha, very little. Haven't developed against Oracle since v7, no admin since v9
April 17, 2009 at 12:34 pm
@Gila Monster:
You said that cursor is working iteratively, My Question is that table variable is also works on iteration. so why only cursor is slow? and not table variable.
April 17, 2009 at 2:09 pm
Wish (4/17/2009)
You said that cursor is working iteratively, My Question is that table variable is also works on iteration
Table variables work on iteration?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2009 at 2:46 pm
I think you may be confusing table variables with something else. Or using them in a way I'm not familiar with. Table variables are not inherently slow. They have other problems, but slowness isn't the one that matters.
- 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
April 17, 2009 at 2:55 pm
A cursor is a structure that you acts on data. A table is a collection of data. A temp table or table var are also collections of data. They don't act in any manner. You determine how you access a table (or table var/temp table). It can be through a cursor or with SQL code that operates on a set of data.
If you think a cursor is like a table, you are missing a fundamental understanding of the system.
April 26, 2009 at 3:48 pm
Thanks for the reply. I was confused by reading so many forums for cursor, which has many different explanation for cursor. I got the answer why cursor is slow. It acquires locks which table variable does not do, and that is why Cursor is slow.
April 26, 2009 at 4:31 pm
Wish (4/26/2009)
I got the answer why cursor is slow. It acquires locks which table variable does not do, and that is why Cursor isslow.
Nope. Cursors are slow because they are a row-by-row method of processing. SQL is optimised for set-based processing, it will be faster to operate on 100 rows in one operation than 1 row 100 times. That is why cursors are slow.
I don't understand what you're saying about table variables. Table variables aren't an alternative to cursors. Table variables are similar to temp tables with different scoping and a few other restrictions. Comparing cursors and table variables is like comparing apples with steering wheels.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply