Is a Temporary Table Really Necessary?
-
> You can't create an index on a table variable
But you can do this...
declare @t table(x int not null primary key clustered)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I've three uses for temp-tables
1. Splitting up queries where complex predicates confuse the optimizer
2. Passing datasets between stored procedures
3. As a worktable in procs where the return set is the result of multiple complex processes.
#1 can usually be solved by using inline-views, CTEs or table valued functions.
#2 can be solved by using table variables, but their use is limited if the dataset is large, and the tablespec has to be implemented as a type.
#3 is usually not solved any better with other methods (non-temp tables or table variables)
For my latest sp's I've started to use...
begin try drop table #temp end try begin catch exec admin.logerror @@PROCID, @@ERROR end catch
...before creating and after using the temp table. Works like a charm 🙂
PS: admin.logerror is configured to ignore "table does not exist" in certain procedures
-
Mark (5/30/2008)
> You can't create an index on a table variableBut you can do this...
declare @t table(x int not null primary key clustered)
Yeah, but it's pretty rare that the data that you would need in a temp table would be useful clustered by a single column... or, at least, it is for me 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone -
Good article.
I use temporary tables and table variables and have found that within transaction table variables are faster. However, if large amounts of data are being used then temporary tables can be faster.
I always prefer to avoid temporary tables if possible and would have written the initial query as:
SELECT table1.numCustID, table2.strPhoneNumber, table3.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN dbo.table2 table2 ON (table1.numBillID = table2.numBillID AND table2.strPhoneNumber = '5555555555')
INNER JOIN dbo.table3 table3 ON (table2.numBillDtlID = table3.numBillDtlID AND table3.strPhoneNumberCalled = '1234561234')
WHERE table1.numCustID = '5555'
ORDER BY table3.dtmCalled DESC
I had thought that equated to pokitlok's query but looking at the execution plan they all equate to the same plan. Would this change on a bigger set of sample data (I tried it joining three tables each with approx 100K rows)?
-
If you read up on the Microsoft article, there are pros and cons to temporary tables as well as table variables.
Specifically:
Q5: Do I have to use table variables instead of temporary tables?
A5: The answer depends on these three factors:
• The number of rows that are inserted to the table.
• The number of recompilations the query is saved from.
• The type of queries and their dependency on indexes and statistics for performance
Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure
Its all about horses for courses....
-
FYI: The link to your Optimizing Stored Procedure Recompiles article results in a 404, at least for me.
Ron Moses
ConEst Software Systems
-----
a haiku...NULL is not zero
NULL is not an empty string
NULL is the unknown -
Various comments:
1. I don't like the "NEVER use", "ALWAYS use", etc. that appears in these articles. For one thing, this was written before SQL Server 2005. What proof is there that the original query needs a temporary table in SQL Server 2005? As software evolves and improves, things that were a "no no" are now OK. The most classic example of this (improvement) was with the handling of large IN lists by the query optimizers of the major RDBMS vendors (DB2, Oracle, etc.). But that is another discussion.
2. Yes, I've had to use, at times, temporary tables to get around faults in the query optimizer of all of the major RDBMS vendors (I've been writing SQL queries since 1984.)
But before deciding to use a temporary table, ensure that the cost of creating a temporary table, populating it with a query, re-querying the same data again from a temp table, destroying the table, etc. is less than the original query. So each situation is different.
3. As for table variables, I use them quite a bit. I use them whenever I need to utilize a small array (scratch pad) within my stored procedures. One principle use is the output of a pivot function to transform a delimited list of values (because you cannot pass an array of values into a procedure or function in SQL Server 2005!). These are small sets and generally the entire set is processed by subsequent queries. OK, so it's performing a table scan of a small in-memory (yeah, I know it may also be in tempdb) table.
4. Related to the above and "why would you create a PK on a table variable?"
Often enough. In the case of my delimited values pivot function, for business logic reasons, I need to know the order that the values appear in the list. So certain table variables may have an IDENTITY column defined.
5. Scoping -- yes, table variables are within the scope of the procedure. This is actually one of the benefits of table variables vs. #temp tables. If the "temp" table needs to remain past a stored procedure, use a #temp table. All part of the design.
6. The lack of "SELECT INTO" (create the table and then populate) for table variables is just nit picking. You can populate a table variable with INSERT INTO ... SELECT ... FROM ... So you have to predefine the table variable. Big deal! Good coding would dictate that anyway. Besides, if you do declare the table variable, you can include IDENTITY columns, as well as a PRIMARY KEY constraint.
In summary, don't discount #temp tables, ##temp tables, table variables, derived tables (sub-queries), CTEs, or any other "tool" in your SQL "toolbox". As the old saying goes... The proper tool for the job
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font] -
"...may cause performance issues due to the locking of the tempdb while the temporary table is being created"
Is it possible to use table hints to alleviate this issue?
-
jmapledoram (5/30/2008)
"...may cause performance issues due to the locking of the tempdb while the temporary table is being created"Is it possible to use table hints to alleviate this issue?
The lock is held for the duration of the creation and population. Just create the #temp table first. Then populate with a separate INSERT INTO ... SELECT statement.
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font] -
JohnG (5/30/2008)
Various comments:4. Related to the above and "why would you create a PK on a table variable?"
Often enough. In the case of my delimited values pivot function, for business logic reasons, I need to know the order that the values appear in the list. So certain table variables may have an IDENTITY column defined.
Right... and having a PK defined on an in-memory small data set is going to make loads of difference.... having an IDENTITY, yes. PK - can't see it making much difference at all.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone -
PK - can't see it making much difference at all.
Again, a typical "off the cuff" remark without thinking. I'm not saying ALL the time. One thing that a PK gives you is duplicate key detection (constraint violation). Yes, even for a table variable! You use it when the logic dictates its use.
DECLARE @v_temp_table TABLE (foobar int PRIMARY KEY CLUSTERED NOT NULL)insert into @v_temp_table values (1);
insert into @v_temp_table values (1);
[font="Courier New"]Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK__#02FEA01F__03F2C458'. Cannot insert duplicate key in object 'dbo.@v_temp_table'.[/font]
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font] -
And another typical post thinking you're amazing and that we're all idiots.
Of course it's going to be useful for enforcing duplicate checking, if you're inserting duplicates in there in the first place. But what I was saying is that it would be rare to find that useful. Maybe you find it useful all the time because you love inserting duplicates or something. Personally I wouldn't use it too much. And, basically, from a performance standpoint (which is where I was coming from in the first place), it will make as much difference as trying to stop a nuclear meltdown with water and a bucket.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone -
Yet another good article on the use of temp tables. And once again I think it highlights the fact that you can't generalize what is or isn't good practice.
Sure table variables can be faster BUT ONLY if you are using them for small data sets. I've also seen SQL decide not to persist data in the tempdb for a temp table.... so it's never cut and dry.
The key things I would take from this article are:
1 Modulate your code
2 Test what works best for a particular situation, e.g. derived tables vs temp tables etc
3 If you are going to use temp tables maxamise their perfomance with proper indexing etc
Thats my two cents anyway... 🙂
-
Matt Whitfield (5/30/2008)
And, basically, from a performance standpoint (which is where I was coming from in the first place), it will make as much difference as trying to stop a nuclear meltdown with water and a bucket.Well, somebody decided to test that theory to prove it or not and found that it can improve performance. See http://msdn.microsoft.com/en-us/library/aa175774(SQL.80).aspx
And before you discount the article, you may wish to take a serious look at the author's resumé.
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font] -
This is a good article. Could you do something besides temp tables? Yes, and the author points this out. Are there draw backs to using temp tables? Yes there can be as with any other choice that one may make. just to add to the author's points, hardware configurations can have a tremendous impact on performance. This is where testing is so important. Developers are often left to do the best they can with what is given. Even in the best of shops, there are limitations that cannot be overcome and thus the "best" way to write a query may not be available.
Q
Please take a number. Now serving emergency 1,203,894
Viewing 15 posts - 46 through 60 (of 93 total)
You must be logged in to reply to this topic. Login to reply