select count(*) vs keep a counter

Question

Assuming indexes are put in place, and absolute-count-accuracy is not necessary (it's okay to be off by one or two), is it okay to use:

Option A

select count(*) 
  from Table 
 where Property = @Property

vs

Option B

update PropertyCounters
   SET PropertyCount = PropertyCount + 1  
 where Property = @Property

then doing:

select PropertyCount 
  from PropertyCounters 
 where Property = @Property

How much performance degradation can I reasonably expect from doing select count(*) as the table grows into thousands/millions of records?

Answer 1

Keeping a separate count column in addition to the real data is a denormalisation. There are reasons why you might need to do it for performance, but you shouldn't go there until you really need to. It makes your code more complicated, with more chance of inconsistencies creeping in.

For the simple case where the query really is just SELECT COUNT(property) FROM table WHERE property=..., there's no reason to denormalise; you can make that fast by adding an index on the property column.

Answer 2

You didn't specify the platform, but since you use T-SQL syntax for @variables I'll venture a SQL Server platform specific answer:

count(*), or strictly speaking would be count_big(*), is an expression that can be used in indexed views, see Designing Indexed Views.

create view vwCounts
with schembinding
as select Property, count_big(*) as Count
from dbo.Table
group by Property;

create unique clustered index cdxCounts on vwCounts(Property);

select Count 
from vwCount with (noexpand)
where Property = @property;

On Enterprise Edition the optimizer will even use the indexed view for your original query:

select count_big(*)
from Table
where Property = @property;

So in the end you get your cake and eat it too: the property is already aggregated and maintained for your for free by the engine. The price is that updates have to maintain the indexed view (they will not recompute the aggregate count though) and the aggregation will create hot spots for contention (locks on separate rows on Table will contend for same count(*) update on the indexed view).