Fast Way To Estimate Rows By Criteria

I have seen a few posts detailing fast ways to "estima开发者_如何学编程te" the number of rows in a given SQL table without using COUNT(*). However, none of them seem to really solve the problem if you need to estimate the number of rows which satisfy a given criteria. I am trying to get a way of estimating the number of rows which satisfy a given criteria, but the information for these criteria is scattered around two or three tables. Of course a SELECT COUNT(*) with the NOLOCK hint and a few joins will do, and I can afford under- or over-estimating the total records. The probem is that this kind of query will be running every 5-10 minutes or so, and since I don't need the actual number-only an estimate-I would like to trade-off accuracy for speed.

The solution, if any, may be "SQL Server"-specific. In fact, it must be compatible with SQL Server 2005. Any hints?

There is no easy way to do this. You can get an estimate for the total number of rows in a table, e.g. from system catalog views.

But there's no way to do this for a given set of criteria in a WHERE clause - either you would have to keep counts for each set of criteria and the values, or you'd have to use black magic to find that out. The only place that SQL Server keeps something that would go into that direction is the statistics it keeps on the indices. Those will have certain information about what kind of values occur how frequently in an index - but I quite honestly don't have any idea if (and how) you could leverage the information in the statistics in your own queries......

If you really must know the number of rows matching a certain criteria, you need to do a count of some sort - either a SELECT COUNT(*) FROM dbo.YourTable WHERE (yourcriteria) or something else.

Something else could be something like this:

• wrap your SELECT statement into a CTE (Common Table Expression)
• define a ROW_NUMBER() in that CTE ordering your data by some column (or set of columns)
• add a second ROW_NUMBER() to that CTE that orders your data by the same column (or columns) - but in the opposite direction (DESC vs. ASC)

Something like this:

;WITH YourDataCTE AS
(
   SELECT (list of columns you need),
      ROW_NUMBER() OVER(ORDER BY <your column>) AS 'RowNum',
      ROW_NUMBER() OVER(ORDER BY <your column> DESC) AS 'RowNum2'
   FROM
      dbo.YourTable
   WHERE
      <your conditions here>
)
SELECT * 
FROM YourDataCTE

Doing this, you would get the following effect:

• your first row in your result set will contain your usual data columns
• the first ROW_NUMBER() will contain the value 1
• the second ROW_NUMBER() will contain the total number of row that match that criteria set

It's surprisingly good at dealing with small to mid-size result sets - I haven't tried yet how it'll hold up with really large result sets - but it might be something to investigate and see if it works.

Possible solutions:

• If the count number is big in comparison to the total number of rows in the table, then adding indexes that cover where condition will help and the query will be very fast.

• If the result number is close to the total number of rows in the table, indexes will not help much. You could implement a trigger that would maintain a 'conditional count table'. So whenever row matching condition added you would increment the value in the table, and when row is deleted you would decrement the value. So you will query this small 'summary count table'.