Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


ms sql server - #temp table performance....

Posted on 2011-10-13
Medium Priority
Last Modified: 2012-05-12
we've got #temp_tables that dont use the proper syntax:


But select right into it

Select x, y,z
      Into #TempTable
      From (
      SELECT .....

it works..but I think that there would be a performance drg, plus there are any DROP TABLE #temp_table at the end of the SP...as the intention is that SQL SVR will blow it away once the SP is done excuting...but we have had performance issues...

does it make that big of a difference?
Question by:GlobaLevel
  • 2
LVL 25

Accepted Solution

jogos earned 2000 total points
ID: 36966065
Drop is not necessairy but always nice to have it.
Create is better (and more understandable in future)

Temp_tables consume write-time in tempdb and mostly don't have indexes so if they contain a large number of rows it will be slow. So when you reference that same temptable several times it could be worth creating an index.
Already considered a table-variable?
http://social.msdn.microsoft.com/Search/en-US?query=tsql%20table%20variable&beta=0&ac=8  when indexes are not needed see here for some advantages or here for the comparison http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server
LVL 25

Expert Comment

ID: 36966394
The temp table you are creating at the beginning is of no use, considering the fact that there are no data inserted into it apart from the select into. Both temp table, the one manually created and the other with select into spells differently. So, create statement can be commented out.
When you are saying performance problem, what do you mean to say? Are you seeing any tempdb contention, where you get waiting resource to be something 2:x:x ?
And as jogos said, its better to create indexes on the table.
LVL 25

Expert Comment

ID: 36966803
Maybe this link can show you why temp tables are not always a good idea

Using the create-statement is better
- for documentation, it's better to see what you are doing and with which datatypes you are working
- when creating it in the in the insert-statement you lock system tables (table definition in temp-db) for the duration of your insert-statement so you could be holding up other processes when it's about a lot of records

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question