Speed of COUNT() in large tables

  • Thread starter Thread starter MDW
  • Start date Start date
M

MDW

I'm working on an app in MS Access that I'm going to shoot
out to a Web page. There are two ways I can do it, one of
which involves finding the COUNT() value on a table and
displaying it on a Web page (I prefer this method b/c it
gets me more information - the other method involves
retreiving a single value from a one column, one row
table). Is there a point, as the table gets bigger (say
100,000 + records), that this COUNT would take a great
amount of time to execute?
 
Since you weren't clear on your "shoot out" method, it's hard to give a
solid answer to the question. In theory, the more the data grows, the
slower queries will become, but there's no way to determine milli vs. mega
seconds at this point.

Since this post isn't a total bomb, I'll offer that Count(*) is typically
faster than using an explicit field name.

Finally, if data from the tables is an issue, you can always create an OLAP
structure that you can use to generate web stats or reports.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
OK, a bit of clarification:

I'm trying to create a hit counter for my Web page. The
fast and dirty option is to have the Web page read in the
value of the current count, add one to it, then update the
database and display the updated result on the page.

The other option we're playing with is, for each page
visit, insert the date and time of the visit into the
database. Thus, we'd have one record in the DB for each
visit (we'd periodically flush the DB, of course, but we'd
probably build this records over a period of one to three
months, so we'd accumulate a decent number of records).
Thus, to find the hit count, we'd grab the COUNT() of that
field.

We're kind of interested in the date/time option because
it would allow us to compile reports about when the
traffic is highest, etc.

But, once the DB grows to 100,000 + rows, we're worried
that the amount of time it would take to fetch the value
from a COUNT() statement would be overly long.
 
As long as your not actually pulling out data and just coutning, Access can
count from 1 to 100,000 in no time. I've run counts on several millions of
records in a blink of an eye. Unless you put in a criteria, counting is
quick. Besides, the web page will probably take longer to load then for
Access to do the count. Another option is to use both methods you
mentioned. Keep a table with just the count and a table that logs every
visit. Then you can clean out the table of visits and your count won't have
to be reset.

Kelvin
 
Back
Top