0

I have been doing some .net performance tests on a bunch of different data access frameworks.

One of the things I've noticed is that performance when accessing the tempdb table in SQL is much quicker than when using a regular table. I'm not too sure why this is but it appears that when entityFramwork (one of the frameworks) sets up its connection with 

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

The performance is reduced to that of a regular table.

Does anyone have any idea why this would be?

undefined
  • 127
  • 5

1 Answers1

0

The BOL help page for tempdb lists the performance improvements made for accessing tempdb.

That said, there are two settings in your list that will have impact on how code is executed: implicit_transactions and the transaction isolation level.

Since read committed is the default transaction isolation level, my guess would be that setting implicit_transactions to off somehow forces tempdb accesses through an extra step that is avoided when doing implicit transactions.

Test it to be sure.

EDIT: as an aside, consider doing really time-critical temporary calculations in a table variable; this is memory-only and hence a factor 100 to 1000 faster than tempdb...

The downside is that there is an obvious point of diminishing returns, as table variables don't have indexes.

adaptr
  • 16,576
  • 23
  • 34
  • The piece about table variables being memory-only isn't always true. Under memory pressure table variables can also be paged out. Another downside compared to temp tables is that no statistics and no indexes other than the primary key can be created. https://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx?Redirected=true – ghoerz Apr 03 '12 at 15:22
  • Yes, they can be paged out.. at which point you should know your database well enough to avoid it :) – adaptr Apr 03 '12 at 15:27
  • 1
    Table variables aren't in memory structures. They are physical objects that are flushed to disk just like any other temp table in the tempdb database. They are different because they have no stats, can't have indexes other than the primary key, etc. – mrdenny Apr 03 '12 at 23:06
  • The usual MS behavior is implicit transactions OFF and READ COMMITTED. So, I think that is a red herring. If you are reading (or writing) a lot of data from (to) tempdb, the underlying files may simply be on faster storage. If you are only dealing with a small number of rows, differences between temporary tables and table variables seem to be highly variable between specific situations (data types, size, recompilation or not, even different servers). IOW, sometimes table variables are faster than temp tables and sometimes table variables are slower than temp tables. YMMV, test to be sure. – Darin Strait Apr 04 '12 at 15:44