May 14, 2009 at 2:15 am
Hi Again,
It seems my research got last so I am going to start again today when I get chance.
The last remember was that with my data if my query plans where cached then the speed difference from a million rows was not that much, but there were still multiple table scans when using a large index.
With regards to using COUNT(ColName), that should not make any difference from what I understand.
Here is a great article that one of our fellow SSC friends wrote.
http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 14, 2009 at 6:05 am
Ok did some more research and asked around (thanks Gail).
It seems that the scan counts are more than what would expect basically because of the degree of Parallelism the Optimizer has chosen for this particular SELECT COUNT(*) query.
There is a query hint option that can override the system configured degree of parallelism.
So I would be interested to if you could drop the non clustered index that you created. (the one that help speed up the query)
Then change you query to this:
SELECT COUNT(*) FROM YourTableName OPTION (MAXDOP 1)
Could you please then post the results you get ?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 15, 2009 at 6:51 am
Really interesting Chris! I also read Gail's article, and it explain perfectly the how and why of the count, and I was mistaking to think counting a column would help! It sounds obvious now that I read what Gail stated.
Thank you all for a better understanding of the problem.
Cheers,
J-F
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply