table variable killer

Question

table variable killer

tcronin 95651

SSCertifiable

Points: 6735
More actions
July 15, 2014 at 12:54 pm

#309078

Started at new place has performance issues with 3rd party app. The 3rd party people love table variables all over the place. Every time we get a release it kills us. I have seen Ozar prophet how bad these things are, I actually rewrote two of their sp's and changed to #temp tables. Time went from 48 seconds to 1 on the last change. Anyone else seen these issues. Server is pretty beefy, plent of memory and processors, I noticed the table variables seem to kil tempdb. Is this any better in 2012, or is just the whole table vairable idea becoming a bad one.

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

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

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

Little better in some ways in 2014. Still should probably be avoided.

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

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 2

keep telling 3rd party that, it's like some kind of company standard one person did it so now everyone has to use them. What is amazing I can run the SP with no one on the system and it sucks. The most famous like "it ran fast in development................."

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 3

one of the sp's had 22 table variables some of them could contain a few hundred rows. The thing only MS could explain is what the engine does. I noticed that the logical reads for the same data (table variable vs #temp) were significantly worse for the table variable, in some cases tens of thousands of logical reads. Even if I put the same index on each still different. I am sure on of the MS engine guys could explain

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

They probably test on two rows.

Good luck. This isn't a technical problem, maybe get management involved. Yours and theirs.

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

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 5

got play with the hand I am dealt, it at least keeps me on my toes