December 6, 2011 at 10:44 pm
Hi Guys,
Any one please let me know which one is better in memory utilizations in sql server 2005
Thanks in Advance....
December 7, 2011 at 12:04 am
Neither, not a factor in this debate.
December 7, 2011 at 12:15 am
Not in memory utilization but off course in terms of its existence (life) in memory and Execution Plan re-usability (recompilation threshold) Table Variables are better.
December 7, 2011 at 12:23 am
Dev (12/7/2011)
in terms of its existence (life) in memory and Execution Plan re-usability (recompilation threshold) Table Variables are better.
As long as you don't mind poor-terrible performance in return (in many cases)
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
December 7, 2011 at 12:27 am
GilaMonster (12/7/2011)
Dev (12/7/2011)
in terms of its existence (life) in memory and Execution Plan re-usability (recompilation threshold) Table Variables are better.As long as you don't mind poor-terrible performance in return (in many cases)
Yup... Pros & Cons 🙂
December 7, 2011 at 12:37 am
In short, you should use Table Variables when it has to contain very small chunk of temporary data (i.e. few rows only). If the temporary data size is huge, you should go with Temporary Tables.
December 7, 2011 at 3:35 am
hope both gives better performanace than cursors.
Regards
Durai Nagarajan
December 7, 2011 at 5:04 am
durai nagarajan (12/7/2011)
hope both gives better performanace than cursors.
It depends on what you're doing with them.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 7, 2011 at 5:08 am
Divine Flame (12/7/2011)
In short, you should use Table Variables when it has to contain very small chunk of temporary data (i.e. few rows only). If the temporary data size is huge, you should go with Temporary Tables.
I've seen a proc go from 50M reads to less than 2M.
That 1 single @t had only 2 rows in it.
December 7, 2011 at 5:38 am
Ninja's_RGR'us (12/7/2011)
Divine Flame (12/7/2011)
In short, you should use Table Variables when it has to contain very small chunk of temporary data (i.e. few rows only). If the temporary data size is huge, you should go with Temporary Tables.I've seen a proc go from 50M reads to less than 2M.
That 1 single @t had only 2 rows in it.
That's an eye opener :w00t: I don't know what developer was doing with those 2 rows :hehe:
December 7, 2011 at 5:48 am
Divine Flame (12/7/2011)
Ninja's_RGR'us (12/7/2011)
Divine Flame (12/7/2011)
In short, you should use Table Variables when it has to contain very small chunk of temporary data (i.e. few rows only). If the temporary data size is huge, you should go with Temporary Tables.I've seen a proc go from 50M reads to less than 2M.
That 1 single @t had only 2 rows in it.
That's an eye opener :w00t: I don't know what developer was doing with those 2 rows :hehe:
WHERE Items.ItemCategoryCode IN (SELECT CategoryCode FROM @CaTs)
Convert to #tbl with PK to help the stats, search and replace to find the 5-10 occurances and boom, 98% reads improvements ;-). Around 50% duration improvement as well.
December 7, 2011 at 5:56 am
Ninja's_RGR'us (12/7/2011)
WHERE Items.ItemCategoryCode IN (SELECT CategoryCode FROM @CaTs)Convert to #tbl with PK to help the stats, search and replace to find the 5-10 occurances and boom, 98% reads improvements ;-). Around 50% duration improvement as well.
Will Table Variable @tbl with PK not work?
December 7, 2011 at 5:58 am
Dev (12/7/2011)
Ninja's_RGR'us (12/7/2011)
WHERE Items.ItemCategoryCode IN (SELECT CategoryCode FROM @CaTs)Convert to #tbl with PK to help the stats, search and replace to find the 5-10 occurances and boom, 98% reads improvements ;-). Around 50% duration improvement as well.
Will Table Variable @tbl with PK not work?
No stats on @t.
That made the whole difference.
December 7, 2011 at 6:03 am
Ninja's_RGR'us (12/7/2011)
Dev (12/7/2011)
Ninja's_RGR'us (12/7/2011)
WHERE Items.ItemCategoryCode IN (SELECT CategoryCode FROM @CaTs)Convert to #tbl with PK to help the stats, search and replace to find the 5-10 occurances and boom, 98% reads improvements ;-). Around 50% duration improvement as well.
Will Table Variable @tbl with PK not work?
No stats on @t.
That made the whole difference.
Do we really need stats for 2 rows? 😀
December 7, 2011 at 6:06 am
Dev (12/7/2011)
Ninja's_RGR'us (12/7/2011)
Dev (12/7/2011)
Ninja's_RGR'us (12/7/2011)
WHERE Items.ItemCategoryCode IN (SELECT CategoryCode FROM @CaTs)Convert to #tbl with PK to help the stats, search and replace to find the 5-10 occurances and boom, 98% reads improvements ;-). Around 50% duration improvement as well.
Will Table Variable @tbl with PK not work?
No stats on @t.
That made the whole difference.
Do we really need stats for 2 rows? 😀
YES. Especially in filters. This will byte you in the arse so hard when you filter on skewed data distribution.
1 filter might return 1 % of the data, but the next one 25%. The QO needs to be able to know about that. Can't do it without stats on that #tbl. The PK then tells him that there's no duplicate value (especially useful in joins).
Viewing 15 posts - 1 through 15 (of 59 total)
You must be logged in to reply to this topic. Login to reply