Performance of "SELECT COUNT(*) FROM X WHERE Y" queries

I have a web application which has queues with different views for different
users. The site has several hundred users a day. With enough RAM in the
server (the application effectively living in RAM) is it quite reasonable
and ok performance wise to be able to use "SELECT COUNT(*) FROM X WHERE Y"
queries (inside an SP) to generate the counters on each page load or should
you use a trigger or perhaps another approach - like a middle tier component
to cache the values?
Thanks
RichardPerformance of a SELECT COUNT(*) query is depends largely on available
indexes. In you example, a non-clustered index on Y will provide an
efficient method to get the count and performance will be roughly
proportional to the number of qualifying rows.
I suggest you run a simple performance test with your worst-case scenario.
If response time is acceptable, you're done. It's best not to implement
techniques like caching or redundant data to address a performance problem
you don't have.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Hollis" <richard_hollis@.hotmail.com> wrote in message
news:%23W2x5UEZFHA.2996@.TK2MSFTNGP10.phx.gbl...
>I have a web application which has queues with different views for
>different
> users. The site has several hundred users a day. With enough RAM in the
> server (the application effectively living in RAM) is it quite reasonable
> and ok performance wise to be able to use "SELECT COUNT(*) FROM X WHERE Y"
> queries (inside an SP) to generate the counters on each page load or
> should
> you use a trigger or perhaps another approach - like a middle tier
> component
> to cache the values?
> Thanks
> Richard
>