Doubt on Temporary tables

Question

Post reply

Doubt on Temporary tables

sunny-141957

Right there with Babe

Points: 746
More actions
July 6, 2005 at 9:39 am

#68152

Sub: My doubt is regarding the usage of temporary Tables
The scenario is I was checking the difference in execution time
of the usage of temporary tables and table variables in 2
different procedures.
When I checked the Estimated Execution Plan on the procedure
with the table variable it displayed it fine.
But when i checked the Estimated Execution Plan on the procedure
with temporary table it displayed error specifying the name of
the temporary table.
Are temporary tables not to be used when displaying estimated
execution plan.
Can anyone point out, what would be the reason for that error.
regards
shown

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You're right, only the 2nd part of this query fails on estimated query plan.

Declare @a table (name varchar(50) not null primary key)

Select * from @a

GO

Create table #a (name varchar(50) not null primary key)

Select * from #a

DROP TABLE #a

Looks like you have to execute the query to fetch the actual plan.

sunny-141957 Right there with Babe Points: 746 More actions · Answer 2

Mr Remi,

I didn't get u'r last sentence.

Please will U make it more clear.

regards

shown

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You can't use the estimated query plan when you have a temp table. You have to execute the query and see the real execution plan (ctrl-k) to see which one is fasted.

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 4

Wasn't there a thread (or two) recently on temp tables vs. table variables? I recall the gist was SQL Server implements them similarly, so you don't really get any perf benefits with one or the other, except where the use of an index would help.

There is no "i" in team, but idiot has two.

sunny-141957 Right there with Babe Points: 746 More actions · Answer 5

That means we can use it with real execution plan but not

with Estimated Plan.

Is it being set like that for some hidden reason.Or is it a bug

in SQL SERVER.

regards

shown

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

The question is about estimated execution plan... The performance question will surely come in later .

noeld SSC Guru Points: 96590 More actions · Answer 7

And no is not a bug is "by design"

Well to be fair the execution plan with tem tables will be dependent on how many rows (Inserts /Updates/deletes) are affecting that table which is not known until run time. If it varies a lot from one run to the next, procedure recompilations take place (where you may get a new plan )

* Noel

sunny-141957 Right there with Babe Points: 746 More actions · Answer 8

Mr Dave,

The main reason I used this performance checking was because I

actually so an article in SQLSERVERCENTRAL

"The ARRAY In SQL Server 2000" by Mr.Alex Grinberg

saying Table variables are faster than Temporary tables.

regards

shown

noeld SSC Guru Points: 96590 More actions · Answer 9

Not necessarily so

have a look: http://support.microsoft.com/default.aspx?scid=kb;en-us;305977

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Here's a recent discussion on the topic we had recently :

http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=120&messageid=182804

sunny-141957 Right there with Babe Points: 746 More actions · Answer 11

I am honoured to be a part of this discussion on the

performance of table variables and temporary tables.

Thank U very much for the feedback,as i have cleared a

doubt and studied something new.

From this I have studied something else too.That is

not to believe or study something just by seeing a single

article.

regards

shown

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ninja's_RGR'us

SSC Guru

Points: 294069

July 6, 2005 at 10:47 am

#571387

You got that right .

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 13

I would agree that table variables will *USUALLY* be better than temp tables, the original question was about getting an execution plan.

You can't get an execution plan for a table that doesn't exist. If your batch includes CREATE TABLE (temp or otherwise), you have to execute the CREATE TABLE statement to be able to get an execution plan for the commands that refer to the table.

Rick Lowrey Right there with Babe Points: 785 More actions · Answer 14

But once you have created your temp table, before you get your execution plan, be sure to go through the steps to populate the table. Otherwise the optimizer won't give you realistic results (ran across this once when a developer was scratching his head trying to find a performance bottleneck in an SP).