July 18, 2005 at 7:34 pm
I'm sure Kalpesh Thaker is a nice person, and I realize that there are only so many ways of describing the same information, particularly in an article such as the one in question. I would like to post Ken Henderson's original text here so that you can see what I am talking about, but that would actually put me in violation of his copyright because I don't have permission from the publisher. But it is widely available, so you can check for yourself. In any case, there is no benefit of the doubt, for there is no doubt. Thaker's article is a virtual copy, with nearly identical syntax and a substitution every other sentence or so that doesn't change the meaning. It wouldn't have taken him much longer to write his own article than it probably took him to type it in while reading it from the book -- *maybe* he saved himself an hour or two. Even the sample SQL at the end has been ripped off. It's plagiarism at a high school level. Too blatant for my taste, and that's why I said something.
July 19, 2005 at 9:12 am
I think that I'll check it out then. Do you have a page number that I can reference so I don't have to search around? If this is the case then the editors of this site need to know that they have posted copyrighted material...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 19, 2005 at 10:46 am
pp. 569-571 in my copy -- the section entitled 'Optimizing Cursor Performance'. There *are* 2 or 3 sentences that contain information that is -in addition- to the information plagiarized from the Henderson article, but the Henderson article is present in its entirety.
July 19, 2005 at 12:08 pm
Tyler, I had a chance to check this out and you are absolutely correct. I'll let Steve Jones know.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 20, 2005 at 10:32 am
thanks dcpeterson. sorry to be the bearer of unpleasant tidings.
July 24, 2005 at 6:17 pm
Apologies for this. I have removed the article. If links are broken, that it why.
November 21, 2009 at 7:11 am
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
November 21, 2009 at 7:59 pm
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.
November 22, 2009 at 3:43 pm
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.
November 22, 2009 at 4:32 pm
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..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 23, 2009 at 12:45 pm
Bru Medishetty (11/22/2009)
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..
What you said is true, but it seems to me that the question wasn't between a set-based query and cursor, but between a query in a for loop thus making it a RBAR operation rather than set-based.
The question seems to spring from a commonly held belief that if you need a RBAR operation (you almost never do) you are better off "rolling your own" cursors by using a loop that by using a cursor. My testing of this has proven (to me) that if you really need RBAR operation, you are likely better off using a real cursor as opposed to "rolling your own."
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
November 23, 2009 at 5:41 pm
I agree with that whole heartedly, DC. I've found that a well written cursor will frequently beat a home grown While Loop just as you said. I'll also add that I think of While Loops as nothing more than "home grown cursors" and have resorted to calling them "cursors" even though that's probably a bit confusing to most. Based on that, allow me to correct what I said in the post previous to this one just to be clear... there are exceptions to every rule but all forms of RBAR (not just cursors) should be avoided if possible. I'll also add that, given a little thought, it's usually possible to avoid them without resorting to just another form of RBAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply