temp table creation over normal sql query
Hi Team, I have an issue some of our report sql are taking a longer time to execute. is temp table creation to insert the data and query that table will help as there are multiple select statement need to be executed? basically i want temp table Vs normal select statement performance. Regards, Eshwar
May 20th, 2011 12:26am

Hi Eshwar, Your requirement is very abstract; meaning to say, there is not sufficient information available to conclude on anything. There are times when Temp tables can be used, but they should not be used straight away as an option to improve performance without checking upon other alternatives. The underlying SQL query might not be constructed properly resulting in a longer query execution time. You might want to spend sometime on checking if the right indexes are available on the underlying tables, or you could go in for covering indexes and much more; Join Hints could also be looked at. But like I said before, its a very relative issue and its hard to say which is right over what with very little information. Moreover, you could try addressing your SQL tuning queries in the SQL Server T-SQL forums, there are real good gurus out there who would love to help you. Just my 2 cents. HTH! Cheers Please mark correct answers :)
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2011 4:46am

Performance of select statement is little better than that of temp tables. People use temp tables for their convinience mostly.Regards, Amey
May 23rd, 2011 8:48am

Performance of select statement is little better than that of temp tables. People use temp tables for their convinience mostly.Regards, Amey
Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2011 8:54am

Hi User_SQLServer, Just as Maey said, temp tables may cause lower performance rather then select statement. In general, temp tables should be avoided, if possible. Because they are created in the tempdb database, they create additional overhead for SQL Server, slowing overall performance. As an alternative to temp tables, consider the following alternatives: •Rewrite your code so that the action you need completed can be done using a standard query or stored procedure, without using a temp table. •Use a derived table. •Consider using a table variable. •Consider using a correlated sub-query. •Use a permanent table instead. •Use a UNION statement to mimic a temp table. For more information about the topic, please see the following articles, Eliminate the Use of Temporary Tables For HUGE Performance Gains: http://www.sql-server-performance.com/2002/derived-temp-tables/ Temp Table Performance Tuning Tips: http://www.sql-server-performance.com/2004/temp-table-tuning/ If you have any question, please feel free to ask. Thanks, Eileen
May 24th, 2011 3:55am

I would add , check out defined indexes on the table, as proper defined indexes can speed up dramatically performance of the query Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Free Windows Admin Tool Kit Click here and download it now
May 24th, 2011 4:13am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics