The Old Joel on Software Forum: Part 4 (of 5) - Temp table or not in SQL Server

Temp table or not in SQL Server – what's best?

I have a situation when in SQL Server, in a stored procedure, I need to store some data in a table for a short while for intermediate processing. All the records can have a unique ID (let’s call it UniqueID), which is guaranteed not to be used by any other thread running. This allows me to use:

a) a temporary table.
b) a pre-created table, which has the UniqueID as part of the key. The stored procedure will look like this:

CREATE PROCEDURE sp_MyProc (@UniqueID INT) AS

……
-- Delete any pre-existing records
-- Notice that TableTemp is not temporary (no #)
DELETE FROM TableTemp WHERE UniqueID = @UniqueID

…..

INSERT INTO TableTemp (UniqueID, …..)
VALUES (@UniqueID, …….)

……

UPDATE TableTemp
SET …..
WHERE …..
AND UniqueID = @UniqueID

……

SELECT ….. FROM TableTemp
WHERE ……
AND UniqueID = @UniqueID

-- Delete records after execution, so that no junk is left
DELETE FROM TableTemp WHERE UniqueID = @UniqueID

END

I expect to have 5 to 20 records per UniqueID and up to 5 UniqueIDs at any single moment, so the total number of records would be from 5 to 100. In this case, is it better to use a temp table, or let the different threads share a pre-created table as in the example above?

http://www.alexlechuck.com
Tuesday, May 18, 2004

Try it and see? One could imagine advantages
for either approach. Depends on the implementation.

son of parnas
Tuesday, May 18, 2004

You may want to look into @table variables. T
They cause fewer stored proc recompiles, slower growth of tempdb trans log (the entries get trucated after use), but can't be indexed.

Yo
Tuesday, May 18, 2004

You should consider using a table variable, as the number of records is quite small.

Perhaps you could avoid using explicit storage altogether by combining your various SQL statements into a single set operation. I don't understand what you are really trying to accomplish here.

To answer the original question: unless this stored procedure is called very often, I would probably use a temp table or table variable instead of a pre-created table. The reason is to reduce external dependencies and obfuscation at the cost of a slightly higher cost per-call.

Tuesday, May 18, 2004

Regarding Yo's comment, saying table variables can't be indexed. This is true to the extent that you can't use "CREATE INDEX".

However, you can create indexes on the table variable via a primary key and/or a unique index. You can also manipulate the clustering of these indexes. An example is below:

declare @foo table (id int, name varchar(100), primary key nonclustered (id) , unique clustered (name))

Tuesday, May 18, 2004

With the number of records you have I doubt the performance is going to be much of an issue whatever approach you take.

I recommend the temp table because a temp table is more of encapsulated approach where the procedure controls all of its objects. Having a permenant table just for doing calculations seems to me to be like creating a global array, instead of a local array for a function because its a bit faster.

john
Tuesday, May 18, 2004

I understand that when you create a temp table in a sql server 2k proc, it has to recompile the proc every time the proc is called - which means that only one user can call the proc at a time. (Its something to do with the optimization process...)
If you have lots of users, and the proc takes a bit of time, this causes a bit of a bottleneck (especially if your DB server doesn't have much memory)

Apparently table variables don't cause this issue ...

Heres more info anyway:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp

Cheers

Bram Borak
Tuesday, May 18, 2004

Thank you all for your input. Apparently the table variable is the best solution. I have implemented it this way.

http://www.alexlechuck.com
Tuesday, May 18, 2004

>Apparently the table variable is the best solution

You don't know shit yet. Test it, then you'll know.

anonymous
Wednesday, May 19, 2004

Well, this article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp

suggests that table variables will cause no recompilation. Also, the documentation says "Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.". So, it _seems_ that they should be faster. Thus the use of the word "Apparently".

http://www.alexlechuck.com
Wednesday, May 19, 2004