Joining to local tables vs #temp tables

We have a warehousing stored procedure that creates #temp tables (some of these have up to 100k rows), then creates indexes on them and uses them to join to other #temp tables or regular tables (some of regular have up to 15M rows on them).

I do not know if it worth the effort to convert the #temp tables to regular tables.

Hence my questions:

  • Is there a performance difference when joining tables across databases on the same server vs. tables on the same database?

  • Are there differences between joining to regular tables vs. #temp tables?

more ▼

asked Dec 09, 2011 at 06:58 AM in Default

avatar image

Raj More
1.8k 83 89 90

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

There is no difference in the way SQL Server deals with temp tables and regular tables in terms of how to join on them etc. You can basically see them as regular tables inside your connection context, the only special ability they have, is to be automatically dropped when the connection is closed.

Temp tables receive the same "attention" as regular tables in terms of index and stats maintenance, so I don't see how you would get a direct benefit just by changing them to regular tables.

Where you may see a difference is in terms of where load on the I/O subsystem could be spread. The temp tables are created in tempdb, so they may be created, populated and queried on different disks than the regular tables in a user database. That may give you a performance advantage, but maybe not. That depends on your system's hardware configuration.

Another point to consider is that tempdb is a "scratch pad" for a lot of other processes. Memory spills, index rebuilds (potentially), spooling, online index operations, row-versioning and some other stuff. These things could cause the temp tables inside tempdb to perform worse than regular tables. Again, it depends on your system though, as some of these things may be negligible or non-critical.

I'm sure there are some other things to consider, but basically you will have to run some tests and baselines to see what is best for you, in this exact scenario, with the load/system usage etc. taken into account.

more ▼

answered Dec 09, 2011 at 07:10 AM

avatar image

26.2k 18 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 09, 2011 at 06:58 AM

Seen: 1488 times

Last Updated: Dec 09, 2011 at 06:59 AM

Copyright 2018 Redgate Software. Privacy Policy