February 25, 2011 at 2:08 pm
Thanks, that clarified some of the guidelines for when it might be useful.
February 25, 2011 at 2:22 pm
there is also a free downloadable ebook at red-gate called "sql server statistics". It states the magical figure of 100 rows.
It also has some statements on the differences of @ and # tables concerning statistics,..
And of course is provides a nice elementary insight on statistics.:kiss:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 25, 2011 at 6:02 pm
ALZDBA (2/25/2011)
It states the magical figure of 100 rows.
Does it also state where that 'magical' figure comes from?
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
February 25, 2011 at 6:33 pm
GilaMonster (2/25/2011)
ALZDBA (2/25/2011)
It states the magical figure of 100 rows.Does it also state where that 'magical' figure comes from?
I'd be shocked if it did...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 25, 2011 at 6:35 pm
WayneS (2/25/2011)
GilaMonster (2/25/2011)
ALZDBA (2/25/2011)
It states the magical figure of 100 rows.Does it also state where that 'magical' figure comes from?
I'd be shocked if it did...
Do you know?
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
February 26, 2011 at 1:17 am
GilaMonster (2/25/2011)
ALZDBA (2/25/2011)
It states the magical figure of 100 rows.Does it also state where that 'magical' figure comes from?
Nope.
Page 24 mentions the estimated rowcount 0 or 1 for @TV and the 100 rows as a rule of thumb to switch from @TV to #tb.
For a guideline that seems acceptable.
Most of my devs haven't got a clue about the number of rows they expect.
( we're not the only ones hearing that )
I just follow the guideline and test it if it doesn't perform well enough.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 27, 2011 at 1:22 pm
TheSQLGuru (2/25/2011)
For a small number of rows you won't see any signifiacnt performance diference,
That is absolutely not true in all cases.
You're correct, my statment isn't true in all cases, but an excetion can be found for almost any generalised statement, which is what mine was.
I've done a significant amount of work with # and @ tables on a large project I worked on a few years ago and did find that @ tables in the 50 to 150 row range tended to perform at least as well and sometimes better than # tables, but once the rows got up to 200 and 20000 rows # tables were better. Unfortunately I was on a 3rd party database so I couldn't create prmanent tables. Subsequent to this work I also cam across the magic 100 rows comment, but from another source.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply