Are temp tables better than table variables

Question

Are temp tables better than table variables

Viewing 15 posts - 16 through 30 (of 37 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Jeff, just so you (and everyone else) knows: :exclamation: UDF usage was an even GREATER cause of horrible performance than table variables at all 3 of those clients I mentioned!! :exclamation:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Heh... No doubt... I can see the RBAR on steroids now... some of my favorite misuses are SPLIT/CONCATENATION functions in batch code, UDF's that format dates so they can be joined, Hierarchical UDF's, and UDF's that return a running total or running count for a single row using either a cursor or triangular join. Yeah... gotta love things like that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

The most common issue I have seen believe it or not is simply referencing a table UDF in a FROM clause. Anything more than a simple select therein (and WOW have I seen some doooOOOOZZZYYY UDFs!!) seems to always lead to 1 as estimated row count out of UDF and thus the same ol' nested loop, kajillion logical read query plans. MS gave developers a 6 shooter with 5.83 bullets in it and let them play Russian Roulette IMNSHO!!

Hey, more business for me!!! 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jeff Moden (3/18/2008)
I'm sorry that Microsoft ever added them to the mix. I'd much rather they had allowed the use of Temp Tables in UDF's especially since you can make a TempTable using SELECT/INTO and you can't with a Table Variable. But, that's just an opinion... 😉

I feel the same way sometimes. Especially when I hear misinformed people touting the 'advantages' of table vars (not persisted to disk, faster than temp tables etc). Usually none of their 'advantages' are even remotly true.

It's going to get worse in SQL 2008, since it allows table variables to be passed as stored procedure parameters. I can see a couple cases where that would be good, and such an opportunity for misuse it's not even funny.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

OK, I hear poor old table variables sobbing in a corner somewhere, so I have to step in here... :w00t:

Table variables do cause less overhead in terms of locking, logging and recompilation than temp tables. (Less recompilation overhead is the upside of absent stats.)

Also:

Table variables are not part of an outer transaction, so they are not subject to outer transaction rollbacks.

Table variables differ in scope: table variables are only visible in the local batch (I think someone already mentioned this earlier), whereas temp tables are visible in the current and inner levels. This makes them the ideal choice in cases where an (attempted) change needs to be audited (say in a trigger), even if the transaction was subsequently rolled back.

I have to admit though that I'm surprised the issue arose with small data sets. My faith in table variables has been shaken somewhat, and I will be less forceful in singing their praises in the future... 🙂

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

If you have small datasets AND any joins you do to the table variable do not result in large numbers of joined rows, then you are perfectly OK. You will get nested loops and they will be most efficient. But when you have 50 rows in the table var and you join to a table and come out with 3M rows and join that to other tables, you (rather your server) are dead meat.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

TheSQLGuru (3/19/2008)
If you have small datasets AND any joins you do to the table variable do not result in large numbers of joined rows, then you are perfectly OK. You will get nested loops and they will be most efficient. But when you have 50 rows in the table var and you join to a table and come out with 3M rows and join that to other tables, you (rather your server) are dead meat.

That's an excellent point, never thought about it that way. Thank you!

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 8

I should mention, while we are on the subject, "INSERT EXEC" does work with table variables in SQL 2005!

This is something I only realized recently.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

Marios Philippopoulos (3/19/2008)
OK, I hear poor old table variables sobbing in a corner somewhere, so I have to step in here...
I should mention, while we are on the subject, "INSERT EXEC" does work with table variables in SQL 2005!
This is something I only realized recently.

Heh... I'd have never known... 😉 I only use temp tables (unless it's a UDF where I can't) and haven't run into a Recompile problem yet because I don't mix the DDL with the DML... I do all the DDL at the very beginning like BOL suggests. Most of the time, GUI related code doesn't need anything temporary anyway. A recompile on a batch job is expected just because the data is more than 5 minutes old.

So far as "blocking" goes... whatcha gonna block with a TempTable? Most "blocking" is left over wives tales from when that type of blocking occurred way back in 6.5. It's all been fixed since 7.0

Whatcha gonna rollback if you use a TempTable? Purpose of TempTable code like that is to get all of the data together before you even think of starting a transaction that might rollback. I never have had the need to include TempTable population code in an explicit transaction.

But, I do understand your points. I just haven't run into any of those problems in the last 11.5 years. First 1/2 year was a bugger because of the locks 6.5 put on TempDB but, like I said, those problems were all fixed in 7.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 10

Jeff Moden (3/19/2008)
Heh... I'd have never known... 😉 I only use temp tables (unless it's a UDF where I can't) and haven't run into a Recompile problem yet because I don't mix the DDL with the DML... I do all the DDL at the very beginning like BOL suggests. Most of the time, GUI related code doesn't need anything temporary anyway. A recompile on a batch job is expected just because the data is more than 5 minutes old.
So far as "blocking" goes... whatcha gonna block with a TempTable? Most "blocking" is left over wives tales from when that type of blocking occurred way back in 6.5. It's all been fixed since 7.0
Whatcha gonna rollback if you use a TempTable? Purpose of TempTable code like that is to get all of the data together before you even think of starting a transaction that might rollback. I never have had the need to include TempTable population code in an explicit transaction.
But, I do understand your points. I just haven't run into any of those problems in the last 11.5 years. First 1/2 year was a bugger because of the locks 6.5 put on TempDB but, like I said, those problems were all fixed in 7.

If you say so!

At the time that 6.5 came out, if someone had mentioned SQL Server to me I would have thought it was a drink!.. 😛

Obviously you guys are talking from experience, I'll take your word for it.

As for explicit transactions, are you saying there has been no case where you had an explicit transaction opened in which you are making a nested sproc call, and in the nested sproc you are creating a temp table??? I find that hard to believe.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

I've got an MS URL that explains the blocking in 6.5 somewhere. Lemme see if I can dig it up... I've always gotta prove these things so people don't think I'm off my rocker...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

I have no problem using table variables if the rowcount is low (100 rows is around my threshold) or I'm just going to do a straight select * from @tbl with no joins or where clause.

The only case I've had with blocking on temp tables was back in SQL 2000 when I had extensive blocking on page 2:1:3 (that's one of the allocation pages in TempDB) because too many temp tables were getting created at a time and there was only a single tempDB file. The app that uses this particular server was temp table heavy. Since table variables are entered in the tempDB system tables and are allocated space in TempDB, they too would have cause this kind of blocking.

The problem is, too many people aren't aware of the downsides of table variables, possibly are misinformed as to how they work with TempDB and overuse them.

As for explicit transactions, are you saying there has been no case where you had an explicit transaction opened in which you are making a nested sproc call, and in the nested sproc you are creating a temp table???

Rule of transactions: As short as possible doing just what is required. I try not to call nested procs within a transaction at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 13

Rule of transactions: As short as possible doing just what is required. I try not to call nested procs within a transaction at all.

In our code base we do that a lot. Perhaps it's time to revisit...

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Shorter transactions means less blocking and less chance of deadlocks. In turn means less work to do. Always a good thing. 😀

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 15

Jeff Moden (3/19/2008)But, I do understand your points. I just haven't run into any of those problems in the last 11.5 years. First 1/2 year was a bugger because of the locks 6.5 put on TempDB but, like I said, those problems were all fixed in 7.

Well, not ALL of them. There is still to this day one VERY important tempdb locking issue if you have a high volume of temp object creation/drops. The SGAM (and one other page that escapes me) locking per tempdb file. Can bring a hard-hit OLTP system to it's knees because tempdb can't actually create the objects you are asking for due to locks on allocation page(s). Thus the new Best Practice of one tempdb data file per physical CPU core.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service