Are temp tables better than table variables

Question

Are temp tables better than table variables

Viewing 8 posts - 31 through 37 (of 37 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

GilaMonster (3/19/2008)
Shorter transactions means less blocking and less chance of deadlocks. In turn means less work to do. Always a good thing. 😀

If you're using transactions to reduce the workload on the server, then yes, short transactions are good. If you're using transactions to control data integrity, sometimes you have to roll back larger transactions than is good for performance.

For example, there are times when a Rollback command in a Catch block had better be able to roll back everything the proc has done up to that point, even if that means leaving some transactions open till that point that could otherwise have been closed. It may mean a slower proc, but it may also mean the difference between ACIDity and dirty data.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

GSquared (3/19/2008)
GilaMonster (3/19/2008)
Shorter transactions means less blocking and less chance of deadlocks. In turn means less work to do. Always a good thing. 😀
If you're using transactions to reduce the workload on the server, then yes, short transactions are good. If you're using transactions to control data integrity, sometimes you have to roll back larger transactions than is good for performance.
For example, there are times when a Rollback command in a Catch block had better be able to roll back everything the proc has done up to that point, even if that means leaving some transactions open till that point that could otherwise have been closed. It may mean a slower proc, but it may also mean the difference between ACIDity and dirty data.

Well said.

We all know short transactions are a good thing, but it's not always feasible.

Hence my point earlier about nested sproc calls within a transaction.

__________________________________________________________________________________
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 3

GSquared (3/19/2008)
If you're using transactions to reduce the workload on the server, then yes, short transactions are good. If you're using transactions to control data integrity, sometimes you have to roll back larger transactions than is good for performance.

You missed my point. Keep transactions as short as possible, based on what they must do (data integrity, business requirements, etc). Tha minimum of just what needs to be within an atomic operation. No unnecessary statements in transactions. Appropriate code to rollback as soon as there's a need (don't do 6 updates when after the 1st you know you muust roll back)

Where possible keep transactions within one stored procedure, because it's easier to read, easier to follow what's happening and ultimatly easier to fix when something goes wrong.

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

GSquared SSC Guru Points: 260824 More actions · Answer 4

GilaMonster (3/20/2008)
GSquared (3/19/2008)
If you're using transactions to reduce the workload on the server, then yes, short transactions are good. If you're using transactions to control data integrity, sometimes you have to roll back larger transactions than is good for performance.
You missed my point. Keep transactions as short as possible, based on what they must do (data integrity, business requirements, etc). Tha minimum of just what needs to be within an atomic operation. No unnecessary statements in transactions. Appropriate code to rollback as soon as there's a need (don't do 6 updates when after the 1st you know you muust roll back)
Where possible keep transactions within one stored procedure, because it's easier to read, easier to follow what's happening and ultimatly easier to fix when something goes wrong.

Totally right. And well expressed.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

latheesh.jaya SSChasing Mays Points: 644 More actions · Answer 5

One more major difference is you can never be changed your table design of your table variable if you need in your script once you created. But this is possible in Temperory variable.

So depends on your requirments, you have to choose the better way, still table varible is not a good option for a big dataset

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 6

Jeff Moden (3/19/2008)
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...

I suspect you're thinking of one of these two:

http://support.microsoft.com/kb/153441/EN-US/[/url]

http://support.microsoft.com/kb/q162753/[/url]

Regards,

Jacob

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

Perfect. Thanks Jacob... the first one is one of two I was talking about. Second one has some good info, too... gonna have to test that and see if it's still applicable or not. Thanks again.

--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)

Cliff Jones SSChampion Points: 10547 More actions · Answer 8

TheSQLGuru (3/19/2008)

Jeff Moden (3/19/2008)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.

Good point to bring up here. I think perhaps you are referring to the PFS. As you and Gail bring up the very good point of having multiple data files for tempdb, especially if you have a lot ot temp table usage.