Temporary Tables

Question

Temporary Tables

Viewing 7 posts - 16 through 21 (of 21 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

kiranbgiet (8/11/2008)
hello
temporary tables are least in use as they consume the memory resource
Instead of temporary tables you can use the table variable as the table variable is just like any another variable we declare in sql

Not correct, Kiran... see the link I posted in my previous post. Table Variables and Temp Tables both start out in memory and they both spill over into TempDB...

--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)
Intro to Tally Tables and Functions

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

GSquared (8/12/2008)
Jeff Moden (8/11/2008)
...The biggest problem I've seen with Temp Tables is where people do a CREATE or SELECT/INTO in the middle of a query. If you look at some of the performance tuning hints in BOL, they tell you never mix DDL in DML... do all the DDL at the front of a proc before any DML occurs. If you mix DDL in DML, I can almost guarantee a recompile or two...;) ....
Actually, some of that is changed in SQL 2005. Check out: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EDOAC
There's a section, about 3/4 of the way through the article, on Recompilations due to mixing DDL and DML.
Even if you put all your DDL at the beginning, you get recompiles, in 2000 and 2005, per this article. 2005 just reduces the scope of the recompiles from proc level to statement level.
The whole article is a bit of a heavy read, but worth it.
A closely related article: http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
One of the things it says is:
In SQL Server 2000, when SQL Server recompiles a stored procedure, the entire stored procedure is recompiled, not just the statement that triggered the recompile. SQL Server 2005 introduces statement-level recompilation of stored procedures. When SQL Server 2005 recompiles stored procedures, only the statement that caused the recompilation is compiled—not the entire procedure. This uses less CPU bandwidth and results in less contention on lock resources such as COMPILE locks.

Yep... I agree... will usually cause a recompile even if all the DDL is at the beginning... the goal is, if you're gonna have one, just have one... moving all the DDL to the beginning helps that come true.

--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)
Intro to Tally Tables and Functions

sjsubscribe SSCrazy Points: 2080 More actions · Answer 3

CTE's are very useful, but temp tables have their uses. Speaking from experience, after switching a CTE to temp table in one procedure, the execution time dropped from 40 minutes to 13 seconds. As others have pointed out, temp tables have their place and no amount of CTE goodness can match that.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Heh... glad to see a kindred spirit.:)

--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)
Intro to Tally Tables and Functions

GSquared SSC Guru Points: 260824 More actions · Answer 5

Yep... I agree... will usually cause a recompile even if all the DDL is at the beginning... the goal is, if you're gonna have one, just have one... moving all the DDL to the beginning helps that come true.

In the sample they use in the article I referenced, you end up with one recompile per statement in SQL 2005, regardless of whether the DDL is intermingled or all at the beginning. It's more efficient than 2000, either way.

I'm not quite sure from the article whether DDL-first actually matters in SQL 2005 or not. Haven't had time to really test it for performance since I read that.

- 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

Antonio Eubanks-398475 SSC Rookie Points: 35 More actions · Answer 6

Like anything...it depends on the job you're trying to do. If all you have is a hammer then everything looks like a nail. Each version of temp tables (variable, derived, and physical) have their place in code, and all are better than the dreaded cursor (but even it has it's place, God forbid). The key is to learn about and have all them in your toolbox, so when you have a screw, you won't use a hammer.

To answer your original question, here are some links:

http://qa.sqlservercentral.com/articles/Temporary+Tables/62409/

http://qa.sqlservercentral.com/articles/Basic+Querying/temptablesinsqlserver/1279/

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

http://www.mssqltips.com/tip.asp?tip=1556

purplebirky SSC-Addicted Points: 431 More actions · Answer 7

I wanted to say thank you again for all the information. Gave me alot to look up and read. 😀

[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]