SQL Server – How to Perform Short Circuit Count Query

sql servert-sql

I have a stored procedure that searches for rows given a long list of parameters. Some of the queries result in extremely slow count queries. Is it possible to stop counting after, say, one hundred rows so that I can simply display "100+" for the row count? It's not important that the users know exactly how many rows there are – especially when the result is in the thousands.

Best Answer

Is it possible to stop counting after, say, one hundred rows so that I can simply display "100+" for the row count?

Sure. A query like this should do the trick:

with q as
(
  select top 100 *
  from …
  where …
) 
select count(*) 
from q

Related Solutions

Sql-server – How to check for a null or empty table-valued parameter

A table can't be NULL, nor can a TVP. How do you check if a table is empty? You certainly don't say IF Sales.SalesOrderHeader IS NULL. :-)

IF EXISTS (SELECT 1 FROM @tvp)
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...

Sql-server – why stored procedure performance is very high compared to plain select query

Could you check your execution plans for the procedure/query? The execution plan of a procedure is also compiled and stored in the library section, while most of the times, the one for a query is computed ad-hoc (not always though). But still, the generation of a execution plan, surely doesn't take that long. So maybe, because of expired statistics (for instance), the plan of the query is not optimal, while the one stored in the procedure it is.

In other words, a good starting point are the plans. If you wish, you could post them here. Then you could check the resource profiles under each operation it is executed, to make sure that both of them are able to use the same amount of resources.

Best Answer

Related Solutions

Sql-server – How to check for a null or empty table-valued parameter

Sql-server – why stored procedure performance is very high compared to plain select query

Related Question