tho.pfaff (11/21/2009)
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
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.
Baiscally, cursors will always be slower than "Set Based" queries because cursors overcome and overwhelm the "natural" way that SQL Server operates.
With only 1 exception in 10,000, you should simply avoid cursors.
--Jeff Moden
Change is inevitable... Change for the better is not.