20

At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the DELETE/TRUNCATE commands. The results that I am getting are as follows (times in milliseconds).

@Table Variable  #Temp (delete)  #Temp (truncate)
---------------  --------------  ----------------
5723             5180            5506
15636            14746           7800
14506            14300           5583
14030            15460           5386
16706            16186           5360

Just to make sure I'm sane, this shows that CURRENT_TIMESTAMP (aka GetDate()) is taken at the time of the statement, not of the batch, so there should be no interaction between TRUNCATE/DELETE with the SET @StartTime = CURRENT_TIMESTAMP statement.

select current_timestamp
waitfor delay '00:00:04'
select current_timestamp

-----------------------
2012-10-21 11:29:20.290

-----------------------
2012-10-21 11:29:24.290

It's quite consistent in the jump between the first run and the subsequent runs when DELETE is used to clear the table. What am I missing in my understanding of DELETE? I have repeated this many times, swapped the order, sized tempdb to not require growth etc.

CREATE TABLE #values (
  id int identity primary key, -- will be clustered
  name varchar(100) null,
  number int null,
  type char(3) not null,
  low int null,
  high int null,
  status smallint not null
);
GO
SET NOCOUNT ON;

DECLARE @values TABLE (
  id int identity primary key clustered,
  name varchar(100) null,
  number int null,
  type char(3) not null,
  low int null,
  high int null,
  status smallint not null
);
DECLARE  @ExecutionTime  TABLE(      Duration bigINT    ) 
DECLARE  @StartTime DATETIME,  @i INT = 1; 
WHILE (@i <= 5) 
  BEGIN 
    DELETE @values;
    DBCC freeproccache With NO_InfoMSGS;
    DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate() 
    /****************** measured process ***********************/ 

    INSERT @values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;

    /**************** end measured process *********************/ 
    INSERT @ExecutionTime 
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP) 
    SET @i +=  1 
  END -- WHILE 

SELECT DurationInMilliseconds = Duration FROM   @ExecutionTime 
GO 

-- Temporary table
DECLARE  @ExecutionTime  TABLE(      Duration bigINT    ) 
DECLARE  @StartTime DATETIME,  @i INT = 1; 
WHILE (@i <= 5) 
  BEGIN 
    delete #values;
    -- TRUNCATE TABLE #values;
    DBCC freeproccache With NO_InfoMSGS;
    DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate() 
    /****************** measured process ***********************/ 

    INSERT #values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;

    /**************** end measured process *********************/ 
    INSERT @ExecutionTime 
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP) 
    SET @i +=  1 
  END -- WHILE 

SELECT DurationInMilliseconds = Duration FROM   @ExecutionTime 
GO

DROP TABLE  #values 
SET NOCOUNT OFF;
Improve this question
0

1 Answer 1

Reset to default
20
+100

This difference only seems to apply when the object is a B+tree. When removing the primary key on the table variable so it is a heap I got the following results

2560
2120
2080
2130
2140

But with the PK I found a similar pattern in my tests as well with typical results below.

+--------+--------+---------+-------------------+
| @table | #table | ##table | [permanent_table] |
+--------+--------+---------+-------------------+
|   2670 |   2683 |    9603 |              9703 |
|   6823 |   6840 |    9723 |              9790 |
|   6813 |   6816 |    9626 |              9703 |
|   6883 |   6816 |    9600 |              9716 |
|   6840 |   6856 |    9610 |              9673 |
+--------+--------+---------+-------------------+

My theory is that there there is some optimisation available when doing bulk inserts to local temporary B+ trees that only applies when it does not already have any pages allocated.

I base this on the following observations.

  1. When running various versions of your test code I only saw this pattern with @table_variables and #temp tables. Not permanent tables in tempdb nor ## tables.

  2. In order to get the slower performance it is not necessary to have previously added and removed a large amount of rows from the table. Simply adding a single row and leaving it in there is sufficient.

  3. TRUNCATE deallocates all pages from the table. DELETE will not cause the last page in the table to be deallocated.

  4. Using the VS 2012 profiler shows that in the faster case SQL Server uses a different code path. 36% of the time is spent in sqlmin.dll!RowsetBulk::InsertRow vs 61% of the time spent in sqlmin.dll!RowsetNewSS::InsertRow for the slower case.

Running 

SELECT * 
FROM sys.dm_db_index_physical_stats(2,OBJECT_ID('tempdb..#values'),1,NULL, 'DETAILED')

after the delete returns

+-------------+------------+--------------+--------------------+
| index_level | page_count | record_count | ghost_record_count |
+-------------+------------+--------------+--------------------+
|           0 |          1 |            0 |                  1 |
|           1 |          1 |            1 |                  0 |
|           2 |          1 |            1 |                  0 |
+-------------+------------+--------------+--------------------+

I found that it was possible to reduce the time discrepancy somewhat by enabling trace flag 610.

This had the effect of reducing the quantity of logging substantially for the subsequent inserts (down from 350 MB to 103 MB as it no longer logs the individual inserted row values) but this had only a minor improvement in timings for the 2nd and subsequent @table , #table cases and the gap still remains. The trace flag improved the general performance of inserts to the other two table types significantly.

+--------+--------+---------+-------------------+
| @table | #table | ##table | [permanent_table] |
+--------+--------+---------+-------------------+
|   2663 |   2670 |    5403 |              5426 |
|   5390 |   5396 |    5410 |              5403 |
|   5373 |   5390 |    5410 |              5403 |
|   5393 |   5410 |    5406 |              5433 |
|   5386 |   5396 |    5390 |              5420 |
+--------+--------+---------+-------------------+

From looking in the transaction log I noticed that the initial inserts against empty local temporary tables seem even more minimally logged (at 96 MB).

Notably these faster inserts had only 657 transactions (LOP_BEGIN_XACT /LOP_COMMIT_XACT pairs) compared with over 10,000 in the slower cases. In particular LOP_FORMAT_PAGE operations seem much reduced. The slower cases have a transaction log entry for this for each page in the table (about 10,270) compared with only 4 such entries in the fast case.

The log used in all three cases was as follows (I have deleted the log records for updates to system base tables to reduce the amount of text but they are still included in the totals)

Logging first insert against @table_var (96.5 MB)

+-----------------------+----------+----------------------------------------------+---------------+---------+
|       Operation       | Context  |                AllocUnitName                 | Size in Bytes |   Cnt   |
+-----------------------+----------+----------------------------------------------+---------------+---------+
| LOP_BEGIN_XACT        | LCX_NULL | NULL                                         |         83876 |     658 |
| LOP_COMMIT_XACT       | LCX_NULL | NULL                                         |         34164 |     657 |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL | NULL                                         |           120 |       3 |
| LOP_FORMAT_PAGE       | LCX_HEAP | dbo.#531856C7                                |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_IAM  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_IAM  | dbo.#531856C7                                |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_IAM  | Unknown Alloc Unit                           |            84 |       1 |
| LOP_HOBT_DDL          | LCX_NULL | NULL                                         |           216 |       6 |
| LOP_HOBT_DELTA        | LCX_NULL | NULL                                         |           320 |       5 |
| LOP_IDENT_NEWVAL      | LCX_NULL | NULL                                         |     100240000 | 2506000 |
| LOP_INSERT_ROWS       | LCX_HEAP | dbo.#531856C7                                |            72 |       1 |
| LOP_MODIFY_ROW        | LCX_IAM  | dbo.#531856C7                                |            88 |       1 |
| LOP_MODIFY_ROW        | LCX_PFS  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        158592 |    1848 |
| LOP_MODIFY_ROW        | LCX_PFS  | dbo.#531856C7                                |            80 |       1 |
| LOP_MODIFY_ROW        | LCX_PFS  | Unknown Alloc Unit                           |        216016 |    2455 |
| LOP_SET_BITS          | LCX_GAM  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         84360 |    1406 |
| LOP_SET_BITS          | LCX_GAM  | Unknown Alloc Unit                           |        147120 |    2452 |
| LOP_SET_BITS          | LCX_IAM  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         84360 |    1406 |
| LOP_SET_BITS          | LCX_IAM  | Unknown Alloc Unit                           |        147120 |    2452 |
| Total                 | NULL     | NULL                                         |     101209792 | 2519475 |
+-----------------------+----------+----------------------------------------------+---------------+---------+

Logging subsequent inserts TF 610 off (350 MB)

+-----------------------+--------------------+----------------------------------------------+---------------+---------+
|       Operation       |      Context       |                AllocUnitName                 | Size in Bytes |   Cnt   |
+-----------------------+--------------------+----------------------------------------------+---------------+---------+
| LOP_BEGIN_CKPT        | LCX_NULL           | NULL                                         |            96 |       1 |
| LOP_BEGIN_XACT        | LCX_NULL           | NULL                                         |       1520696 |   12521 |
| LOP_COMMIT_XACT       | LCX_NULL           | NULL                                         |        651040 |   12520 |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL           | NULL                                         |            40 |       1 |
| LOP_DELETE_SPLIT      | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |          2160 |      36 |
| LOP_END_CKPT          | LCX_NULL           | NULL                                         |           136 |       1 |
| LOP_FORMAT_PAGE       | LCX_HEAP           | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        859236 |   10229 |
| LOP_FORMAT_PAGE       | LCX_IAM            | Unknown Alloc Unit                           |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |          3108 |      37 |
| LOP_HOBT_DDL          | LCX_NULL           | NULL                                         |           648 |      18 |
| LOP_HOBT_DELTA        | LCX_NULL           | NULL                                         |        657088 |   10267 |
| LOP_IDENT_NEWVAL      | LCX_NULL           | NULL                                         |     100239960 | 2505999 |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |     258628000 | 2506000 |
| LOP_INSERT_ROWS       | LCX_HEAP           | dbo.#531856C7                                |            72 |       1 |
| LOP_INSERT_ROWS       | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |       1042776 |   10302 |
| LOP_MODIFY_HEADER     | LCX_HEAP           | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        859236 |   10229 |
| LOP_MODIFY_HEADER     | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |          3192 |      38 |
| LOP_MODIFY_ROW        | LCX_IAM            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |           704 |       8 |
| LOP_MODIFY_ROW        | LCX_PFS            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        934264 |   11550 |
| LOP_MODIFY_ROW        | LCX_PFS            | Unknown Alloc Unit                           |        783984 |    8909 |
| LOP_SET_BITS          | LCX_GAM            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         76980 |    1283 |
| LOP_SET_BITS          | LCX_GAM            | Unknown Alloc Unit                           |        534480 |    8908 |
| LOP_SET_BITS          | LCX_IAM            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         76980 |    1283 |
| LOP_SET_BITS          | LCX_IAM            | Unknown Alloc Unit                           |        534480 |    8908 |
| LOP_SHRINK_NOOP       | LCX_NULL           | NULL                                         |            32 |       1 |
| LOP_XACT_CKPT         | LCX_NULL           | NULL                                         |            92 |       1 |
| Total                 | NULL               | NULL                                         |     367438748 | 5119297 |
+-----------------------+--------------------+----------------------------------------------+---------------+---------+

Logging subsequent inserts TF 610 on (103 MB)

+-------------------------+-------------------------+----------------------------------------------+---------------+---------+
|        Operation        |         Context         |                AllocUnitName                 | Size in Bytes |   Cnt   |
+-------------------------+-------------------------+----------------------------------------------+---------------+---------+
| LOP_BEGIN_CKPT          | LCX_NULL                | NULL                                         |           192 |       2 |
| LOP_BEGIN_XACT          | LCX_NULL                | NULL                                         |       1339796 |   11099 |
| LOP_BULK_EXT_ALLOCATION | LCX_NULL                | NULL                                         |         20616 |     162 |
| LOP_COMMIT_XACT         | LCX_NULL                | NULL                                         |        577096 |   11098 |
| LOP_CREATE_ALLOCCHAIN   | LCX_NULL                | NULL                                         |            40 |       1 |
| LOP_DELETE_SPLIT        | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |          2160 |      36 |
| LOP_END_CKPT            | LCX_NULL                | NULL                                         |           272 |       2 |
| LOP_FORMAT_PAGE         | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |        863520 |   10280 |
| LOP_FORMAT_PAGE         | LCX_IAM                 | Unknown Alloc Unit                           |            84 |       1 |
| LOP_FORMAT_PAGE         | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |          3108 |      37 |
| LOP_HOBT_DELTA          | LCX_NULL                | NULL                                         |        666496 |   10414 |
| LOP_IDENT_NEWVAL        | LCX_NULL                | NULL                                         |     100239960 | 2505999 |
| LOP_INSERT_ROWS         | LCX_CLUSTERED           | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |         23544 |     218 |
| LOP_INSERT_ROWS         | LCX_HEAP                | dbo.#719CDDE7                                |            72 |       1 |
| LOP_INSERT_ROWS         | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |       1042776 |   10302 |
| LOP_MODIFY_HEADER       | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |        780216 |   10266 |
| LOP_MODIFY_HEADER       | LCX_HEAP                | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |       1718472 |   20458 |
| LOP_MODIFY_HEADER       | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |          3192 |      38 |
| LOP_MODIFY_ROW          | LCX_IAM                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |           704 |       8 |
| LOP_MODIFY_ROW          | LCX_PFS                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |        114832 |    1307 |
| LOP_MODIFY_ROW          | LCX_PFS                 | Unknown Alloc Unit                           |        231696 |    2633 |
| LOP_RANGE_INSERT        | LCX_NULL                | NULL                                         |            48 |       1 |
| LOP_SET_BITS            | LCX_GAM                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |         77100 |    1285 |
| LOP_SET_BITS            | LCX_GAM                 | Unknown Alloc Unit                           |        157920 |    2632 |
| LOP_SET_BITS            | LCX_IAM                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |         77100 |    1285 |
| LOP_SET_BITS            | LCX_IAM                 | Unknown Alloc Unit                           |        157920 |    2632 |
| LOP_XACT_CKPT           | LCX_NULL                | NULL                                         |            92 |       1 |
| Total                   | NULL                    | NULL                                         |     108102960 | 2602218 |
+-------------------------+-------------------------+----------------------------------------------+---------------+---------+
Improve this answer
5
  • Thanks for the detailed confirmation. So the question still bides, why DELETE isn't returning the table to truly empty, using your term. Also, this would argue for using #temp tables if clear/populate is used in a batch processing loop.
    – 孔夫子
    Commented Oct 21, 2012 at 2:25
  • 1
    @RichardTheKiwi - The benefit of TRUNCATE over DELETE on its own would also argue for that. I would also rarely consider table variables for large numbers of rows.
    – Martin Smith
    Commented Oct 21, 2012 at 10:45
  • This will sound lazy, but wouldn't repeating a 1 to 10-record (variable) insert 1000 times in a batch show the same symptoms? The usage of a large number of rows is only to exacerbate the issue, and provide scale to better see the difference. The gist of the question is to prove one way or another that #temp tables would be better, once we know what the difference is.
    – 孔夫子
    Commented Oct 21, 2012 at 10:51
  • Well my theory is that it is the allocation of the 10,000+ pages which happens in a much more optimized way and seems to avoid some per page overhead. For smaller inserts I would expect any such difference to be less significant.
    – Martin Smith
    Commented Oct 21, 2012 at 10:57
  • @RichardTheKiwi - Thanks! There is probably more to say on this. As I'll try upgrading to the same version as SQL Kiwi and seeing if I still see the different code paths. If so maybe it is hardware dependant that it makes such a difference (my tests have been on my desktop PC with all data and log files on the same SSD)
    – Martin Smith
    Commented Nov 14, 2012 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.