9

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Richard
  • 147
  • 1
  • 2
  • 8

2 Answers2

15

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
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • 10
    It would be more accurate to say the `DELETE` is the better option. Nothing to do with table variables you get the same with a `#temp` table and DELETE. This is quite an edge case in that the 10 rows all fit on one page. `TRUNCATE` deallocates the last page from the table and `DELETE` doesn't. The tables are so tiny so the overhead from logging the deleted rows is less than the overhead from constantly deallocating and reallocating the single page in the table. [For larger tables the story is different](http://dba.stackexchange.com/q/27309) – Martin Smith Jan 08 '13 at 23:35
  • So.... truncate would be better for a larger table with say, 1000 rows? Truncate is typically waaaaaay faster for clearing data than a delete, because it purges it all indiscriminately and doesn't write nearly as much to the log as deleting individual rows. – Triynko Mar 31 '17 at 21:22
9

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    Thanks, I agree the difference between delete and truncate is probably negligible. It was an academic question really, as swapping the temporary tables out for table variables in my procedure made about 10% difference. As "Spender" said I just tested it. – Richard Apr 16 '10 at 12:22
  • I was wondering myself w/ my similar situation as OP. Temp table can be 10, 25, 250 rows depending upon how data is setup. In my test scenario 15 rows I was comparing Actual Execution Plans in a proc that has a temp table that's iterated, then reused again in outer loop. We don't want to have stale data so we clear the temp table with DELETE. This shows up in the execution plans at 4% of the batch (its complex lots going on). If I replace with TRUNCATE its not in the plan at all! Is that normal behavior of truncate (does not show up in exec plans) or is it so trivial its irrelevant to plans? – Jeff Mergler Sep 01 '17 at 19:27