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

Cursor Expand / Collapse
Author
Message
Posted Saturday, November 21, 2009 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 23, 2009 12:58 PM
Points: 2, Visits: 6
hi @all,

i want to know a bit more about cursors and the technical way they are realized. my understanding is, that cursors are pointers on a result-set within the ram. the result-set is small (comparing to the table the rows are from) and the sql which perform the result-set is executed only one time (i hope :o) ), right?
So what we have is a small result-set within a location the cpu has the fastest way to grab rows from this result-set.
why should this cursors (used in for loops) not be more efficient than ordinary sql-queries (in for loops)?

ordinary queries grap the rows from the hard-disk, where the cpu needs more time to grap them. and erery time the sql-query is executed the a full table-scan must be performed to find the row.

abstract:
cursors (used in for loops):
- sql-query to get the result-set is executed one time, to make a result set (in the initialsation-part of procedure)
- one small result-set
- fastest way for a cpu to grap the data, because they are in the ram

ordinary queries (used in for loops):
- executed to get on row per query
- in every execution the whole table has to be scaned to get one row, so there is no result-set to crawl over
- no fast way for the cpu to grap the data, because data is on harddrive

what du you think about what my understanding of cursors is. am i right?
please let me know.

cheers tommy


Post #822885
Posted Saturday, November 21, 2009 8:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
You should know that wether you are using cursors or a while loop it is still RBAR (a Modenism for Row By Agonizing Row) processing. In almost all cases, converting cursor-based solutions to set-based solutions will result in improved performance and scalability of your code.


Instead of learning more about writing cursors, I use that energy to learn how to write set-based solutions. If you must learn about cursors, use that to learn how to convert existing cursor-based solutions to set-based solutions.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #822950
Posted Sunday, November 22, 2009 3:32 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
abstract:
cursors (used in for loops):
- sql-query to get the result-set is executed one time, to make a result set (in the initialsation-part of procedure)
- one small result-set
- fastest way for a cpu to grap the data, because they are in the ram

ordinary queries (used in for loops):
- executed to get on row per query
- in every execution the whole table has to be scaned to get one row, so there is no result-set to crawl over
- no fast way for the cpu to grap the data, because data is on harddrive


I'm not sure who wrote that for you but it's mostly full of hooie... not all cursors are in ram just like not all non cursors are on disk.

Basically, cursors will always be slower than "Set Based" queries because cursors (and While Loops) override the "natural" way that SQL Server operates.

With only 1 exception in 10,000, you should simply avoid cursors.


--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 #823020
Posted Sunday, November 22, 2009 4:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,646, Visits: 1,947
tho.pfaff (11/21/2009)
ordinary queries grap the rows from the hard-disk, where the cpu needs more time to grap them. and erery time the sql-query is executed the a full table-scan must be performed to find the row.


It is not always true that ordinary queries fetches the rows from hard disk.

Regarding full table scan must be performed to find a row, that would be true if the company has a policy of no indexes and would never use an index .... .

Your assumption is wrong Tommy, a full table scan happens when there are no indexes and can be avoided by having appropriate indexes..



Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #823030
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse