Temp table and table variable
in [Programming SQL]
|
From: simon on 26 Jul 2010 04:28 Hi, I have table variable with about 10 columns in my query and I'm using it only once. It has about 2000 rows. Query executes about 5 seconds. If I just change table variable into temp table and leave everything the same, query executes 100ms. How is that possible that it is so big difference in execution time? Maybe, because the query is new, it takes different execution plan now, and after a while the execution time would be the same - if so, there is the problem with statistics. How can I know what is the reason? Regards, Simon
From: Gert-Jan Strik on 26 Jul 2010 12:34 Simon, What is the query you are using? If you something like this: INSERT INTO @my_table_var SELECT ... FROM my_table and then: INSERT INTO #my_temp_table SELECT ... FROM my_table Then the second query will be faster, because a lot of the data that was read in the first query will still be available in the Buffer Cache. If that is not the cause, then please indicate what query you are running. -- Gert-Jan
From: Erland Sommarskog on 26 Jul 2010 16:25 simon (zupan.net(a)gmail.com) writes: > I have table variable with about 10 columns in my query and I'm using > it only once. > It has about 2000 rows. > > Query executes about 5 seconds. > > If I just change table variable into temp table and leave everything > the same, query executes 100ms. > How is that possible that it is so big difference in execution time? > Maybe, because the query is new, it takes different execution plan > now, and after a while the execution time would be the same - if so, > there is the problem with statistics. How can I know what is the > reason? As Gert-Jan says, if it is about filling the temp table/table variable, it's an issue of the data you read, being in cache the second time. If you mean that you use the temp table/table variable in a query together with other table, what you say makes perfect sense. A table variable does not have statistics, so SQL Server will guess 1 row, and have no idea about distribution. (Althougn adding a primary key still can help.) A temp table on the other hand has statistics, so when SQL Server comes to the second query, it will note that statistics has changed and recompile the query, and now find a better execution plan. -- 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 27 Jul 2010 05:02 On 26 jul., 22:25, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > simon (zupan....(a)gmail.com) writes: > > I have table variable with about 10 columns in my query and I'm using > > it only once. > > It has about 2000 rows. > > > Query executes about 5 seconds. > > > If I just change table variable into temp table and leave everything > > the same, query executes 100ms. > > How is that possible that it is so big difference in execution time? > > Maybe, because the query is new, it takes different execution plan > > now, and after a while the execution time would be the same - if so, > > there is the problem with statistics. How can I know what is the > > reason? > > As Gert-Jan says, if it is about filling the temp table/table variable, it's > an issue of the data you read, being in cache the second time. > > If you mean that you use the temp table/table variable in a query together > with other table, what you say makes perfect sense. A table variable does > not have statistics, so SQL Server will guess 1 row, and have no idea about > distribution. (Althougn adding a primary key still can help.) A temp table > on the other hand has statistics, so when SQL Server comes to the second > query, it will note that statistics has changed and recompile the query, and > now find a better execution plan. > > -- > 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 Thank you Erland, it's exactly what have you writed. I'm using table variable in some joins,... and temp table is 100 times faster because of statistics. For simple selects the speed is the same. Table variable is useful only for very small data sets and for transfering in functions or procedures (in sql2008). Any other benefit of table variable? Table variable is also written on disk in temp db and not in ram as many people thinks. Using temp tables is in many cases better solution. Regards, Simon
From: Erland Sommarskog on 27 Jul 2010 17:53
simon (zupan.net(a)gmail.com) writes: > Table variable is useful only for very small data sets and for > transfering in functions or procedures (in sql2008). Any other benefit > of table variable? Yes, since table variables do not have statistics they do not cause recompilations, which can be good for performance. Yes, exactly the same reason why you in other occasions should not use them. There is one more advantage with table variables over temp tables: This procedure is created successfully, despite it refers to a non- existing column: CREATE TABLE alf (a int NOT NULL, b int NOT NULL) go CREATE PROCEDURE alf_sp AS CREATE TABLE #evil (x int NOT NULL) UPDATE alf SET a = e.x FROM alf JOIN #evil ON e.x = a.c go DROP TABLE alf DROP PROCEDURE alf_sp But if you make #evil a table variable, the error is detected at compile- time. Since a table variable is a declare entity, there is no deferred name resolution involved with it. > Table variable is also written on disk in temp db and not in ram as > many people thinks. Correct! > Using temp tables is in many cases better solution. Yup. As you say, table variables are good when the volume is small, but at bigger volumes temp tables are usually better. But I have a very large procedure, around 3000 lines log. 43 table variables, and no temp tables. They were temp tables originally, but at least in SQL 2000, recompilation was a complete killer. -- 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 |