| Author |
Topic |
|
tjmojo
Starting Member
8 Posts |
 Posted - 2002-12-05 : 12:34:14
|
| I know that there are thousands of articles here that discuss the use of cursors, temporary tables, and how to avoid them. There are instances, however, when you cannot avoid using those techniques, and my question is under which (general?) conditions a 'Temp Table' solution is preferable over a 'Cursor' solution and vice versa. Which solution is faster under which conditions? For example, for a small result set, is the overhead of creating a temp table larger than for using a FASTFORWARD cursor? Please list your experiences and specific performance data for tests that you may have performed.'Sub-discussion': I have discovered that sometimes queries for which the execution plan was shown to be more 'costly' than for other queries ran faster than the 'cheaper' queries. Why is that? Is it because of result set caching (if such a thing exists)? How does this pertain to the issue above regarding cursors and temp tables?Thanks for your input in advance. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-05 : 13:08:29
|
| Generally speaking, a cursor should be the absolute last thing you try, when all else, literally all other methods, have been TRIED and have failed or deemed to have unacceptable performance. You can't say in advance which technique will perform best until you test all of them. Factors such as database design, hardware specs, RAM, indexes, etc. affect performance in ways that can't be predicted without testing and measuring.Data caching does play a significant role in query performance. To eliminate it, run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS before you test a particular method. They will flush the procedure and data caches, respectively, and will prevent caching from providing improved performance (another way of saying it is that flushing the caches helps you determine how BADLY something performs) Whatever works best under those conditions will most likely be the best method. |
 |
|
|
tjmojo
Starting Member
8 Posts |
Posted - 2002-12-05 : 14:07:40
|
| Do you have any specific performance data (or a link to an article) that shows how the creation of a temp table compares to the use of a cursor? |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-12-05 : 14:16:55
|
| http://www.sqlteam.com/item.asp?ItemID=5761If you're using sqlsvr2k you might also want to look into table variables.http://www.sqlteam.com/item.asp?ItemID=9454Edited by - ValterBorges on 12/05/2002 14:17:35 |
|
|
|
tjmojo
Starting Member
8 Posts |
Posted - 2002-12-05 : 14:44:54
|
| I was aware of the article by graz comparing cursor performance with alternative solutions. I was interested in seeing data about comparing temp tables with cursors (in cases where there only is a choice between temp tables and cursors, e. g. doing more than one thing with a complex result set such as in my other issue [url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22009[/url]). |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-05 : 14:49:12
|
| Why don't you just TRY both methods and see which performs better? That's the ONLY way you're going to know for certain.(a sense of deja vu is coming over me...I wonder why) |
|
|
|
tjmojo
Starting Member
8 Posts |
Posted - 2002-12-05 : 15:24:05
|
There is a simple answer to this last question: In the last three weeks I have found an answer to virtually any SQL question I had in these forums and your articles. So I got lazy, and abandoned any attempts to do any thinking (or programming for that matter) myself. After all, with suggestions found on these pages, I was able to reduce 150-line cursor routines into pretty 20-line UPDATE/SELECT/sub-SELECT statements. So I thought in the future I really don't have to do any work anymore and just get paid by my current employer for posting questions on SQLTeam and reading through the answers.But I guess my assumption is wrong, so I actually have to do SOME work by myself. Too bad, I just started to like it.  PS: I am a slow typer, which is not necessarily a helpful skill for a programmer. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-12-05 : 15:50:56
|
Good answer tjmojo ! - Jeff |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-12-08 : 21:33:43
|
quote:
So I thought in the future I really don't have to do any work anymore and just get paid by my current employer for posting questions on SQLTeam and reading through the answers.But I guess my assumption is wrong, so I actually have to do SOME work by myself. Too bad, I just started to like it.
Oh, I dunno - still seems to work for me  --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
Discussion: Temporary Tables / Cursors / Sets