Temp table and table variable
in [Programming SQL]
|
From: Erland Sommarskog on 29 Jul 2010 16:53 simon (zupan.net(a)gmail.com) writes: > first thnak you for your answer. > > There in document writes: > "If the statistics object is defined on a temporary table..." > > Who defines statistic object, isn't it created automatically and > always, when you create temp table? A statistics object can be created explicitly with the commands CREATE STATISTICS, CREATE INDEX or automatically by SQL Server under conditions which are described in the white paper. (The exact rules for the latter case escapes me now, but essentially when SQL Server has reason to scan the table anyway.) > So, in this situation, the recompilation won't happened again every > time: > > CREATE PROCEDURE outer_sp AS > CREATE TABLE #temp(a int NOT NULL) > INSERT INTO #temp........--the statistics are also created(always), > because I insert some data into table > SELECT a FROM #temp > DROP TABLE #temp -- it also clear all statistics > > So, each time I execute this procedure, the statistics are created > (and index statistics also, if there is some index), but recompilation > would be done only first time. Recompilation due to deferred name resolution will only happen the first time. Recompilation because of changed statistics can happen on each execution. > What about the scenario, where I have the normal table instead of temp > table: > > CREATE PROCEDURE outer_sp AS > SELECT a INTO temp_userID FROM .....--I must have unique name if > there are more users executing the same procedure, so I add userID > into the name of table -- there is also minimal logging because of > SELECT INTO > SELECT a FROM temp_user > DROP TABLE temp_user > > Is this good solution, do you have any comment? I don't think creating permanent tables in stored procedures is a good idea, since I think a database schema should be fixed, and only change when a new version of the application is installed. Then again, in processes that involves ETL of data warehouses, dropping a recreating a staging table may be a fair game. In this case, it is also usually a fair assumption that there are no simultaneous users running the procedure. > I have another question(sorry to bother you). > > http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx > > Here are the steps when you use a temporary table: > 1) Lock tempdb database > 2) CREATE the temporary table (write activity) > 3) SELECT data & INSERT data (read & write activity) > 4) SELECT data from temporary table and permanent table(s) (read > activity) > 5) DROP TABLE (write activity) > 4) Release the locks That was not a very good article, and there are several errors in it. One is the that the entire tempdb is locked; that's just plain rubbish. There are row locks taken on the involved system tables when the table is created, and if there is a transction in progress the locks are held until the transaction commits. The author's main point seems to be that you should use derived tables rather than temp tables, and he is correct that this can improve performance. However, sometimes you can get better performance if you materialise an intermediate result in a temp table, rather than using one big query. Note also that the article is old; it is from 2002. One more thing: an explicit DROP TABLE of a temp table in a procedure is not to recommend. This may prevent SQL Server from caching the temp table definition, which can have an impact in a high-volume system. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: simon on 30 Jul 2010 03:52 On 29 jul., 22:53, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > simon (zupan....(a)gmail.com) writes: > > first thnak you for your answer. > > > There in document writes: > > "If the statistics object is defined on a temporary table..." > > > Who defines statistic object, isn't it created automatically and > > always, when you create temp table? > > A statistics object can be created explicitly with the commands CREATE > STATISTICS, CREATE INDEX or automatically by SQL Server under conditions > which are described in the white paper. (The exact rules for the latter > case escapes me now, but essentially when SQL Server has reason to scan > the table anyway.) > > > So, in this situation, the recompilation won't happened again every > > time: > > > CREATE PROCEDURE outer_sp AS > > CREATE TABLE #temp(a int NOT NULL) > > INSERT INTO #temp........--the statistics are also created(always), > > because I insert some data into table > > SELECT a FROM #temp > > DROP TABLE #temp -- it also clear all statistics > > > So, each time I execute this procedure, the statistics are created > > (and index statistics also, if there is some index), but recompilation > > would be done only first time. > > Recompilation due to deferred name resolution will only happen the first > time. Recompilation because of changed statistics can happen on each > execution. > > > What about the scenario, where I have the normal table instead of temp > > table: > > > CREATE PROCEDURE outer_sp AS > > SELECT a INTO temp_userID FROM .....--I must have unique name if > > there are more users executing the same procedure, so I add userID > > into the name of table -- there is also minimal logging because of > > SELECT INTO > > SELECT a FROM temp_user > > DROP TABLE temp_user > > > Is this good solution, do you have any comment? > > I don't think creating permanent tables in stored procedures is a good > idea, since I think a database schema should be fixed, and only change > when a new version of the application is installed. > > Then again, in processes that involves ETL of data warehouses, dropping a > recreating a staging table may be a fair game. In this case, it is also > usually a fair assumption that there are no simultaneous users running the > procedure. > > > I have another question(sorry to bother you). > > >http://www.sql-server-performance.com/articles/per/derived_temp_table... > > > Here are the steps when you use a temporary table: > > 1) Lock tempdb database > > 2) CREATE the temporary table (write activity) > > 3) SELECT data & INSERT data (read & write activity) > > 4) SELECT data from temporary table and permanent table(s) (read > > activity) > > 5) DROP TABLE (write activity) > > 4) Release the locks > > That was not a very good article, and there are several errors in it. One > is the that the entire tempdb is locked; that's just plain rubbish. There > are row locks taken on the involved system tables when the table is > created, and if there is a transction in progress the locks are held until > the transaction commits. > > The author's main point seems to be that you should use derived tables > rather than temp tables, and he is correct that this can improve > performance. However, sometimes you can get better performance if you > materialise an intermediate result in a temp table, rather than using one > big query. > > Note also that the article is old; it is from 2002. > > One more thing: an explicit DROP TABLE of a temp table in a procedure is > not to recommend. This may prevent SQL Server from caching the temp table > definition, which can have an impact in a high-volume system. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I created temp table in stored procedure and some queries from that table inside SP. Than I wached events in SQL profiler. Recompilation was only first time as you said. But event Auto stats never happened - does that mean that statistic was not created for that temp table? I inserted 30000 rows of data into temp table and also I created clustered index on it. It should create statistics as I understand. That is the main benefite of temp table vs table variable. Only because of statistic the temp table is faster than table variable. I guess that in this case, the query was not complex enough that optimizer would search for optimal execution plan and in that moment it would create statistics, because it would see, that they are missing. Am I right? So, you suggest that DROP TABLE is not recommended inside the SP. I didn't know that. So the SQL server would drop table automatically when it goes out of scope? I tried and it works ok also without drop statements. Thank you for everything, Simon
From: Erland Sommarskog on 30 Jul 2010 05:16
simon (zupan.net(a)gmail.com) writes: > I created temp table in stored procedure and some queries from that > table inside SP. > Than I wached events in SQL profiler. Recompilation was only first > time as you said. > But event Auto stats never happened - does that mean that statistic > was not created for that temp table? > I inserted 30000 rows of data into temp table and also I created > clustered index on it. It should create statistics as I understand. In which order did you do things? If you first inserted rows, and then added the index, the stats for the index are created with the index. Also, in Profiler were you looking at the Performance:AutoStats event? I will have to admit that I don't know exactly how that event works, because I don't think I've ever traced it. A sure sign of auto-update of statistcs is when you see queries that starts off with "SELECT statman". It's also a good idea to include all events in the Stored Procedures category to see all events. > So, you suggest that DROP TABLE is not recommended inside the SP. I > didn't know that. So the SQL server would drop table automatically > when it goes out of scope? Yes, a temp table created within a scope, is automatically dropped when that scope exists. By the way, another thing that prevents caching of temp-table metadata is when you create explicit indexes on it after creation. (But it's perfectly OK to include PRIMARY KEY and UNIQUE constraints in the CREATE TABLE statement.) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |