table variables vs physical tables performance

Question

table variables vs physical tables performance

sunil88_pal88

SSC Eights!

Points: 834
More actions
November 1, 2014 at 2:24 am

#294826

Hi Guys I have run into a strange issue .
I have a Stored Procedure which is having couple of inner Stored Procedure .In that inner Stored Procedures i am filling some physical table ,that physical tables i am selecting in outer SP and performing cross join.
I tried to modify the Stored procedure ,instead of inner stored procedures i created functions which were returning tables.In outer stored procedure i am applying cross join on the tables returned by functions.
The strange thing i got that is that stored procedure using physical tables was taking much less time (i thought second approach will take less time).
Any suggestions why this has happened

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

Table variables (which are NOT memory-only) don't have statistics. This generally makes them a huge performance problem.

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

sunil88_pal88 SSC Eights! Points: 834 More actions · Answer 2

Thanks for your reply ,if there any way we can make tables variables memory only

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

No you cannot force a table variable to be memory only.

And since they don't have statistics, you are still looking at a performance problem - most of the time - with them.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

No.

Table variables are temp tables with different scoping and a lack of statistics. That's all. They're temp tables. They live in TempDB. They are physical tables and always have been. Any articles saying they are memory-only are wrong and are perpetuating a myth.

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