Re: more problems with count(*) on large table

On Monday 01 October 2007, Mike Charnoky <noky(at)nextbus(dot)com> wrote:
> This is strange... count(*) operations over a period of one day's worth
> of data now take ~1-2 minutes to run or ~40 minutes. It seems that the
> first time the data is queried it takes about 40 minutes. If I try the
> query again, it finishes in 1-2 minutes!
>
> Again, nothing else is happening on this db server except for a constant
> insertion into this table and a few others. I have done "set statistics
> 100" for the evtime field in this table.

The first time, you're reading from disk. The second time, you're reading
from cache. Tens of millions of disk seeks don't come cheap.

--
We're Microsoft. Everything else is just patent infringement.