Is count(*) really expensive?
asp.net, performance, premature-optimization, sql-server-2005
Solution
You need to attach SQL Profiler or an app level profiler like L2SProf and look at the real query costs in your context before:
guessing what the problem is and trying to determine the likely benefits of a potential solution
allowing others to guess for you on da interwebs - there's lots of misinformation without citations about, including in this thread (but not in this post :P)
When you've done that, it'll be clear what the best approach is - i.e., whether the SELECT COUNT is dominating things or not, etc.
And having done that, you'll also know whether any changes you choose to do have had a positive or a negative impact.
Problem
I have a page where I have 4 tabs displaying 4 different reports based off different tables. I obtain the row count of each table using a `select count(*) from <table>` query and display number of rows available in each table on the tabs. As a result, each page postback causes 5 `count(*)` queries to be executed (4 to get counts and 1 for pagination) and 1 query for getting the report content. Now my question is: are `count(*)` queries really expensive -- should I keep the row counts (at least those that are displayed on the tab) in the view state of page instead of querying multiple times? How expensive are COUNT(*) queries ?