Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Things You Didn't Know About Temp Tables and Table Variables Expand / Collapse
Author
Message
Posted Friday, July 18, 2008 11:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 2,840, Visits: 3,867
Jeff Moden (7/18/2008)
[quote]
Again, I wouldn't use a correlated subquery for this. But that's not the big problem here... what does this query do to the @Tab table? Nothing... ID remains the same.


Hi Jeff,

You are correct, I am updating the id with the same value.
But since this is only an example to show the syntax, i would not put too much meaning into this.

But thanks for your hint.


Best Regards,
Chris Büttner
Post #536954
Posted Friday, July 18, 2008 5:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
Thanks Christian...heh... yeah, I was just wondering if the OP understood that. Very strange sample code to begin with...


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #537108
Posted Friday, July 18, 2008 7:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
Adam Machanic (7/18/2008)
Jeff Moden (7/18/2008)
Viswanath S. Dhara (7/18/2008)
I am trying to update a table using a correlated subquery.


Why? Correlated subqueries are a form of RBAR and can actually be worse than a cursor in some cases. Use a correctly formed joined update instead...


Your "correctly formed" update is non-ANSI Compliant and is nondeterministic in some cases. The query optimizer can--and usually does--optimize out correlated subqueries so that they're not reevaluated on a row-by-row basis. Certainly not worse than a cursor.


That would be true if there is only 1 column being updated... each correlation in an Update in SQL Server 2000 (haven't checked 2k5, yet) adds about the same amount of time.

To be clear, correlated sub-queries can produce performance thousands of times worse than a cursor depending on the usage and the data... please see the following...

http://www.sqlservercentral.com/articles/T-SQL/61539/

Admittedly, that type of correlation won't work in an update and I apologize for confusing the issue...

... And, please tell me that your not one of those folks that writes only ANSI compliant code. ;)


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #537115
Posted Friday, July 18, 2008 8:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
By the way... Folks, say hi to Adam Mechanic... he's the guy that first introduced me to the idea of what I currently call a Tally table.

Thanks again for that, Adam.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #537118
Posted Wednesday, September 3, 2008 12:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:47 PM
Points: 32, Visits: 105
Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?


While both the temp table and the table variable will utilize the TempDB essentially the same way (IE. Memory till table size reaches a certain point), the table variable will not write to the transaction log (which is why it is unaffected by Rollbacks). This can decrease the overall I/O used by an operation. Other consideration include statistics. for a table variable, there is always considered to be 1 row by the optimizer, even if there are tens of thousands, which can result in a less than optimum choice for the join (nested loop vs. hash vs. merge). also, Temp Tables can trigger recompiles of stored procedures that create the object, as the acutal execution plan cannot be persisted (table reference doesn' exist until you are executing), which can also be costly.

Post #563290
Posted Friday, September 12, 2008 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 4, 2014 7:45 AM
Points: 15, Visits: 257
There have already been a couple replies on this so I won't belabor too much, but you definitely can create a PK on a table variable, you just can't name it. You can also create an index and apply any appropriate attributes as well (unique, clustered, nonclustered, etc.). SQL Server won't let you name these objects for exactly the reason described: possibility of naming collisions.

As to the questions about advantages/disadvantages, here's my take: The table varaiable will give you a performance boost if you can keep its contents skinny and short (translation, fewest possible columns and fewest possible rows). From what I've read and heard, SQL Server tries its best to keep the table var's data memory-resident and will only "spill-over" into tempdb if surpasses a certain threshold. It also does help to add an index or two to the table var if you are joining, sorting or grouping on column(s). Don't go hog wild with indexes or you'll make too much work for the engine and very quickly lose any advantage you may have gained. The other performance boost comes with the fact that it is not a persisted object. Because it is not included in the transaction it does not add write I/O to the log file. And because it automatically falls out of scope when the batch completes it is very quickly and efficiently deleted without executing an additional statement in your code.

I've used table var's quite a bit over the past couple years and am very happy with the performance. One of my most common uses is when I need to left join a table but I only need a subset of its data. I have had very good results when I load the subset into the table var and then left join that in the parent query.

Speaking of efficiency, please don't forget about table-valued functions! These puppies are executed in their own memory context and are extremely fast. I think that TVF's are one of the most under-utilized great features of SQL 2000/2005. If you're ever thinking you've got an application for a table variable but wish you could filter its contents, there's your answer.
Post #568625
Posted Friday, September 12, 2008 9:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 4, 2014 7:45 AM
Points: 15, Visits: 257
Well, this is why I don't post a whole lot, I sometimes type without thinking! You CANNOT execute any DDL for the table var outside of its declaration, therefore you CANNOT create indexes. Inline constraints are the only option for table var's. If you do need indexes, temp tables may be a better option.

One other way table vars can increase performance is that they tend to trigger fewer recompiles in your sp's...
Post #568672
Posted Friday, September 12, 2008 9:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 9:28 PM
Points: 1,140, Visits: 702
PKs and UNIQUE constraints are backed by indexes, so you can create indexes, as long as they're unique. Need a non-unique index? Add an identity column and use it as a key to create uniqueness... There are ways around virtually any limitation!

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #568676
Posted Tuesday, September 23, 2008 4:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 13, 2011 5:53 AM
Points: 37, Visits: 183
Gr8...thank u for the wounderful article
--
Rijosh
Post #574193
Posted Tuesday, September 23, 2008 7:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
foursaints (9/12/2008)
There have already been a couple replies on this so I won't belabor too much, but you definitely can create a PK on a table variable, you just can't name it. You can also create an index and apply any appropriate attributes as well (unique, clustered, nonclustered, etc.). SQL Server won't let you name these objects for exactly the reason described: possibility of naming collisions.

As to the questions about advantages/disadvantages, here's my take: The table varaiable will give you a performance boost if you can keep its contents skinny and short (translation, fewest possible columns and fewest possible rows). From what I've read and heard, SQL Server tries its best to keep the table var's data memory-resident and will only "spill-over" into tempdb if surpasses a certain threshold. It also does help to add an index or two to the table var if you are joining, sorting or grouping on column(s). Don't go hog wild with indexes or you'll make too much work for the engine and very quickly lose any advantage you may have gained. The other performance boost comes with the fact that it is not a persisted object. Because it is not included in the transaction it does not add write I/O to the log file. And because it automatically falls out of scope when the batch completes it is very quickly and efficiently deleted without executing an additional statement in your code.

I've used table var's quite a bit over the past couple years and am very happy with the performance. One of my most common uses is when I need to left join a table but I only need a subset of its data. I have had very good results when I load the subset into the table var and then left join that in the parent query.

Speaking of efficiency, please don't forget about table-valued functions! These puppies are executed in their own memory context and are extremely fast. I think that TVF's are one of the most under-utilized great features of SQL 2000/2005. If you're ever thinking you've got an application for a table variable but wish you could filter its contents, there's your answer.


Temp tables act the same as table variables when it comes to memory... they, too, are extremely fast when used properly... read Q4/A4 in the following link...


... which says...

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).


Sure, sure... use of a Temp Table normally guarantees a recompile... there's a lot of other things like small changes in data that also guarantee a recompile. Expected lifetime of an execution plan on a busy system is about 5 minutes. AND, table variables are guaranteed to evaluate as a single row table... not always the best thing for an optimal execution plan.

Don't use either a Temp Table or Table Variable based on rules of thumb. Do some testing... both will surprise you under different circumstances.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #574860
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse