Use of temp table

Question

Use of temp table

Virender Sihag

Right there with Babe

Points: 748
More actions
November 17, 2010 at 9:11 pm

#231353

I have query which is using 4 temporary tables for some internal caluclation.
My doubt here is should i use temporary tables or should i use physical tables keeping
in mind that this query is executed in every 10 seconds.
🙂
[font="Arial Narrow"]Failure is not the worst thing. The worst Thing is not to try[/font]

Viewing 15 posts - 1 through 14 (of 14 total)

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

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

virender.singh (11/17/2010)
I have query which is using 4 temporary tables for some internal caluclation.
My doubt here is should i use temporary tables or should i use physical tables keeping
in mind that this query is executed in every 10 seconds.
🙂

I wouldn't use physical tables for such a thing especially if you have something other than the Simple Recovery Model active. Why do you think the use of temp tables here is a problem?

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

Virender Sihag Right there with Babe Points: 748 More actions · Answer 2

thanks for your quick reply jeff.

Although i am already using temp tables, but my boss:):-D suggested me use physical table instead and like you i was also surprised.

[font="Arial Narrow"]Failure is not the worst thing. The worst Thing is not to try[/font]

ps. One Orange Chip Points: 29422 More actions · Answer 3

Temp tables are created in tempdb just like you create any other table in your user database. Only difference is it will be destroyed when u disconnect (Prefixed with #) or it will be destroyed when all connections using that object disconnects(prefixed with ##).

Pradeep Singh

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

ps. (11/18/2010)
Only difference is it will be destroyed when u disconnect (Prefixed with #)

There are other differences internally around logging, caching when dropped and the like.

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

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

virender.singh (11/17/2010)
but my boss:):-D suggested me use physical table instead

Ask him for his reasoning.

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

ps. One Orange Chip Points: 29422 More actions · Answer 6

GilaMonster (11/18/2010)
ps. (11/18/2010)
Only difference is it will be destroyed when u disconnect (Prefixed with #)
There are other differences internally around logging, caching when dropped and the like.

Thanks Gail. Need to search for more info on that. I hope the book by Brent Ozar (sql server professional internals and troubleshooting) gives me some information. it has a chapter on tempdb. will get it in a day or two.

Pradeep Singh

Virender Sihag Right there with Babe Points: 748 More actions · Answer 7

I know that temp tables are created in tempdb by default and automatically dropped on session close.

he was telling that there would be more logging, caching etc. in case of a temp table so try to avoid it if you are executing your query so frequently.

[font="Arial Narrow"]Failure is not the worst thing. The worst Thing is not to try[/font]

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

There's less logging for a temp table compared to a user table. Proof on my blog if you're interested.

The data caching's much the same, but even if it were more, caching is good, so more is better.

Temp tables aren't in tempdb by default, they are always in tempDB, that cannot be changed.

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

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

virender.singh (11/18/2010)
I know that temp tables are created in tempdb by default and automatically dropped on session close.
he was telling that there would be more logging, caching etc. in case of a temp table so try to avoid it if you are executing your query so frequently.

Actually, that's not the best advice. IIRC (Gail, please correct me if I'm wrong), Temp Tables have space reserved and the definition is preserved for a bit if you don't explicity drop the Temp Table. A frequently running routine can reuse that instead of building new. In other words, there is sometimes a performance advantage to using Temp Tables.

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

ps. (11/18/2010)
Temp tables are created in tempdb just like you create any other table in your user database. Only difference is it will be destroyed when u disconnect (Prefixed with #) or it will be destroyed when all connections using that object disconnects(prefixed with ##).

I'd also like to remind everyone that they start out in memory (which usually makes it very fast) and only spill onto disk if it uses too much memory... just like a Table Variable does.

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

Jeff Moden (11/18/2010)
Actually, that's not the best advice. IIRC (Gail, please correct me if I'm wrong), Temp Tables have space reserved and the definition is preserved for a bit if you don't explicity drop the Temp Table.

True, even if you do explicitly drop the temp table.

SQL reserves (I think) one page for the heap/cluster and one page for each index (need to check) as well as the metadata.

This is one of the things that alleviates allocation contention on 2005+, making the problem far less severe than on SQL 2000. There's also implications for recompiles.

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

Paul White SSC Guru Points: 150467 More actions · Answer 12

GilaMonster (11/18/2010)
Jeff Moden (11/18/2010)
Actually, that's not the best advice. IIRC (Gail, please correct me if I'm wrong), Temp Tables have space reserved and the definition is preserved for a bit if you don't explicity drop the Temp Table.
True, even if you do explicitly drop the temp table.
SQL reserves (I think) one page for the heap/cluster and one page for each index (need to check) as well as the metadata.
This is one of the things that alleviates allocation contention on 2005+, making the problem far less severe than on SQL 2000. There's also implications for recompiles.

One data page and one IAM page is cached with the query plan in the plan cache, only if:

* Named constraints are not created.

* Data Definition Language (DDL) statements that affect the table are not run after temp table creation

* The object is not created from dynamic SQL

See http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx

And yes, explicitly dropping the temporary table makes no difference to the caching behaviour.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

GilaMonster (11/18/2010)
Jeff Moden (11/18/2010)
Actually, that's not the best advice. IIRC (Gail, please correct me if I'm wrong), Temp Tables have space reserved and the definition is preserved for a bit if you don't explicity drop the Temp Table.
True, even if you do explicitly drop the temp table.
SQL reserves (I think) one page for the heap/cluster and one page for each index (need to check) as well as the metadata.
This is one of the things that alleviates allocation contention on 2005+, making the problem far less severe than on SQL 2000. There's also implications for recompiles.

Ah... forgot about the recompiles. They can be a blessing or a bane. "It Depends". Thanks, Gail.

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

Paul White NZ (11/19/2010)
GilaMonster (11/18/2010)
Jeff Moden (11/18/2010)
Actually, that's not the best advice. IIRC (Gail, please correct me if I'm wrong), Temp Tables have space reserved and the definition is preserved for a bit if you don't explicity drop the Temp Table.
True, even if you do explicitly drop the temp table.
SQL reserves (I think) one page for the heap/cluster and one page for each index (need to check) as well as the metadata.
This is one of the things that alleviates allocation contention on 2005+, making the problem far less severe than on SQL 2000. There's also implications for recompiles.
One data page and one IAM page is cached with the query plan in the plan cache, only if:
* Named constraints are not created.
* Data Definition Language (DDL) statements that affect the table are not run after temp table creation
* The object is not created from dynamic SQL
See http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx
And yes, explicitly dropping the temporary table makes no difference to the caching behaviour.

Thanks for the confirmation and the time to research it, Paul.

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