I need to show the number of results for a given category, and hide any categories that give no results.
Example: This Yahoo!Jobs page shows the number of results in categories like City, Job Category, Experience etc.
I work in C#/Asp.Net, and fear that our server will choke without some serious caching and sql optimization.
How would you go forward creating a solution like this?
I think you will have to go into a heck of a lot more detail about your set-up. Are you using an ORM? Is the data already in memory? How is the request coming in? What specifically is your concern? Have you done any profiling to determine this is indeed a problem? I cannot stress that last one enough. Ensure that what you're afraid of is an actual problem before you try to find ways around it.
To attempt an answer however, if you do something like this
_categoryRepository
.Where(category=>category.Matches(query))
.Select(category.Items.Count())
Count() will only be executed for categories where category.Matches(query) returns true.
Relational databases are not suited for that kind of stuff. It's just the wrong tool for the job. Instead, take a look at Lucene, Solr, Sphinx, etc.
I personally recommend Solr, it's very easy to get started and with SolrNet you can write a faceted ASP.NET app in a few lines of code.
Disclaimer: I'm the author of said library.
The typical way these counts are done is via caching. The model is to have a trigger on insert into the relevant tables, then update a calculated field with 'CurrentCount'.
You can add further levels of caching into the app itself, so that counts only get incremented every few hours, or similar.
It is significantly better than actually counting all the rows each time, but it does mean you need to know in-advance what sorts of data you will be counting, so you can pre-calculate it.