CODEWITHSUNDEEP
sql-servertemp-tablessql-insert
mrQQ
mrQQ

Reputation: 21

Insert speed Temporary Table vs Normal table

there are many threads about temporary tables vs table variables.

However, my question is, what makes temporary table much faster than normal table in Microsoft SQL Server?

My stored procedure runs 5 times as fast if I insert to temp table, and then just move data out of it into normal table.

The most obvious answer would be keys/indexs, but the normal table is without any keys at all.

What else would make it run faster?

Thanks

Upvotes: 1

Views: 1920

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

Several possibilities:

  1. TempDB could be on faster I/O
  2. Query could be parallel and taking advantage of multiple tempdb data files if you've configured it to do so
  3. TempDB could have room for the inserts while the user database has to wait for an autogrow
  4. User database could have constraints on logging as well for similar reasons
  5. User database might have to "wake up" - e.g. if it has AutoClose set to ON

Upvotes: 4

Related Questions