Databases
StackOverflow2013
Postsdbo
POSQL Server table perf comparison -- temp tables, or table variables? Or something else?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server table perf comparison -- temp tables, or table variables? Or something else?
    primarykey
    data
    text
    <p>In SQL Server, I'm trying to do a comparative analysis between two different table structures with regard to insert performance given different keys. Does it matter if I use a table variable to do this testing, or should I use a temporary table? Or do I need to go to the trouble of actually creating the tables and indexes?</p> <p>Specifically, I'm currently using the following script:</p> <p><code></p> <pre><code>DECLARE @uniqueidentifierTest TABLE ( --yes, this is terrible, but I am looking for numbers on how bad this is :) tblIndex UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, foo INT, blah VARCHAR(100) ) DECLARE @intTest TABLE ( tblindex INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, foo INT, blah VARCHAR(100) ) DECLARE @iterations INT = 250000 DECLARE @ctrl INT = 1 DECLARE @guidKey UNIQUEIDENTIFIER DECLARE @intKey INT DECLARE @foo INT = 1234 DECLARE @blah VARCHAR(100) = 'asdfjifsdj fds89fsdio23r' SET NOCOUNT ON --test uniqueidentifier pk inserts PRINT 'begin uniqueidentifier insert test at ' + CONVERT(VARCHAR(50), GETDATE(), 109) WHILE @ctrl &lt; @iterations BEGIN SET @guidKey = NEWID() INSERT INTO @uniqueidentifierTest (tblIndex, foo, blah) VALUES (@guidKey, @foo, @blah) SET @ctrl = @ctrl + 1 END PRINT 'end uniqueidentifier insert test at ' + CONVERT(VARCHAR(50), GETDATE(), 109) SET @CTRL = 1 --test int pk inserts PRINT 'begin int insert test at ' + CONVERT(VARCHAR(50), GETDATE(), 109) WHILE @ctrl &lt; @iterations BEGIN INSERT INTO @intTest (foo, blah) VALUES (@foo, @blah) SET @ctrl = @ctrl + 1 END PRINT 'end int insert test at ' + CONVERT(VARCHAR(50), GETDATE(), 109) SET NOCOUNT OFF </code></pre> <p></code></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USstack
    1. USstack
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server table perf comparison -- temp tables, or table variables? Or something else?
      1. USGennady Vanin Геннадий Ванин
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload