Compare performance between Cursor and Temp Table

What is the real difference in performance between using a cursor and using a temp table? Will there be a noticeable difference in performance between cursors and temporary table? It appears to me that they are similar. Does the nature of a cursor intrinsically have more overhead? -Thx in advance

Basically is there any advantage to replacing a cursor with a temp table. I would sill need the functionality to process one record at a time but it would be looping through a temp table using a counter. I had planned to use a table variable for this. The data is relatively narrow and would only have 20- 25 rows.

quote:Originally posted by FrankKalis

Cursor work on a row-by-row basis. They process one row at a time. Especially on larger tables they are performance killers and generally are cursors viewed as some kind of SQL of the last resort.
Not sure, what you now mean by temp table, but I suspect you meant importing some base data into such a temp table and then work on the temp table thereafter. If so, temp tables allow you to fully explore the thing RDBMS like SQL Server excel at, that is working on sets at a time. Using the proper SQL commands you should see a massive boost in performance.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Well- I need to process each record as it comes in. The business requirements requires processing one record at a time when it comes in. I don't want to process each record as it comes in so I'll wait a few seconds (10 secs) and scoop up the newly inserted records. There is a field in each record that needs to be parsed. Currently it is done with a cursor. I was thinking of switching to table variable and then looping. I just did know if this was more efficient. I can't think of a way to process one record at a time other than with a cursor or temp table.<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />There are very few occasions I've seen this far, where one really needs to loop through rows or process a row at a time. If you <b>really</b> need to do this, I think there is no point in using a temp table anyway. A cursor or a UDF would be the way to go here. However, you might want to post your code along with an explanation. Maybe there is another way. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

A while loop on a temp table will be faster than a cursor.

Let's face it: there are times when a cursor is your only option.

But there are lots of people writing T-SQL procedures as if they were VB procedures, ignorant of what a single plain old SQL statement can accomplish in one deft swoop that can take easily 10 times as many statements in VB. Multiply that by the number of iterations for doing it row-by-row, and that should give you some idea.

The records come in to fast- 2/sec. Reading the table this many time would probably cause locking. I agree this type of processing belongs on the client or some external component outside of SQL. But you know how it is-they expect SQL to perform miracles. At this point I'm trying to minimize the load on the server by switching out cursors with looping through temporary tables. This will get the developers enough time to develop this process outside of SQL. - Thx for all your advice.

quote:Originally posted by FrankKalis

SET ROWCOUNT 1 also processes only one row.
But how is the data coming in? Can't you do the parsing at the client?

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Thanks- That's what I needed to know.

quote:Originally posted by Argyle

A while loop on a temp table will be faster than a cursor.

quote:A while loop on a temp table will be faster than a cursor.

Processing one row at time? Not quite sure about that, at least I don't expect significant improvement. It would be nice to have feedback about experiment result.

In your case just 20-30 rows the performance difference is not going to matter much.

quote:Originally posted by mmarovic

quote:A while loop on a temp table will be faster than a cursor.

Processing one row at time? Not quite sure about that, at least I don't expect significant improvement. It would be nice to have feedback about experiment result.

Some tests:
http://www.sqlteam.com/item.asp?ItemID=5761

Depends on how many rows you work with though if you gain on the initial creation time of the temp table. A while loop directly on the original table would be the best option.