I have a stored procedure inside which I create a temporary table that typically contains between 1 and 10 rows. This table is truncated and filled many times during the stored procedure. It is truncated as this is faster than delete. Do I get any performance increase by replacing this temporary table with a table variable when I suffer a penalty for using delete (truncate does not work on table variables)
Whilst table variables are mainly in memory and are generally faster than temp tables do I loose any benefit by having to delete rather than truncate?
Running the followign to scripts, it would seem that the Table Variable is the better option
CREATE TABLE #Temp(
ID INT
)
DECLARE @Int INT,
@InnerInt INT
SELECT @Int = 1,
@InnerInt = 1
WHILE @Int < 50000
BEGIN
WHILE @InnerInt < 10
BEGIN
INSERT INTO #Temp SELECT @InnerInt
SET @InnerInt = @InnerInt + 1
END
SELECT @Int = @Int + 1,
@InnerInt = 1
TRUNCATE TABLE #Temp
END
DROP TABLE #TEMP
GO
DECLARE @Temp TABLE(
ID INT
)
DECLARE @Int INT,
@InnerInt INT
SELECT @Int = 1,
@InnerInt = 1
WHILE @Int < 50000
BEGIN
WHILE @InnerInt < 10
BEGIN
INSERT INTO @Temp SELECT @InnerInt
SET @InnerInt = @InnerInt + 1
END
SELECT @Int = @Int + 1,
@InnerInt = 1
DELETE FROM @Temp
END
From Sql Profiler
CPU Reads Writes Duration
36375 2799937 0 39319
vs
CPU Reads Writes Duration
14750 1700031 2 17376
Quite frankly, with only 10 or 20 (or even 100) entries, any difference in speed would be in a sub-nanosecond realm. Forget about it - don't even waste a second of your brain time on this - it's a non-issue!
In general
table variables will be kept in memory up a certain size - if they go beyond that, they're swapped out to disk in the tempdb
database, too - just like temporary tables. Plus: if a temporary table has only a handful of entries, they'll most like be stored on a single 8k page anyway, and as soon as you access one of the entries, that entire page (and thus the whole temporary table) will be in SQL Server memory - so even here, there's really not a whole lot of benefits to table variables...
table variables don't support indices nor statistics, which means if you have more than a handful of entries, and especially if you need to search and query this "entity", you're better off with a temporary table
So all in all : I personally use temporary tables more often than table variables, especially if I have more than 10 entries or something like that. Being able to index the temp table, and having statistics on it, usually pays off big time compared to any potential gain a table variable might have, performance-wise.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With