Faster select count(*)

About 50% of the search time is spent just counting the number of rows in
the resultset.
We page the resultset and show only one page at a time. Is there any way we
can count
the number of rows in the resultset faster?
CasperMore information, please -- counting what result set? Are you doing this on
the client side? Can you show the code you're currently using?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OcflLTjLFHA.3000@.TK2MSFTNGP10.phx.gbl...
> About 50% of the search time is spent just counting the number of rows in
> the resultset.
> We page the resultset and show only one page at a time. Is there any way
we
> can count
> the number of rows in the resultset faster?
> Casper
>|||select rowcnt from sysindexes
where id = object_id(N'[table]') and indid = 1
(indid = 1) is PK constraints I believe, so may not work for heaps; other
indexes don't appear to track rowcnt. Also the rwcnt field I don't think is
kept 100% current, so probably not accurate enough for your needs. But it's
there anyways ;)
- KH
"Casper Hornstrup" wrote:

> About 50% of the search time is spent just counting the number of rows in
> the resultset.
> We page the resultset and show only one page at a time. Is there any way w
e
> can count
> the number of rows in the resultset faster?
> Casper
>
>|||http://www.aspfaq.com/2120
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OcflLTjLFHA.3000@.TK2MSFTNGP10.phx.gbl...
> About 50% of the search time is spent just counting the number of rows in
> the resultset.
> We page the resultset and show only one page at a time. Is there any way
we
> can count
> the number of rows in the resultset faster?
> Casper
>|||> select rowcnt from sysindexes
> where id = object_id(N'[table]') and indid = 1
That will give you a rough count of what's in the entire table. However,
the OP said they wanted the count of the resultset... which is likely a
combination of a join between multiple tables and a filtered where clause.|||Yea, the original question is pretty ambiguious.
In the scenario you suggest using @.@.ROWCOUNT after the select might work for
this application.
It all depends eh? ;)
- KH
"Aaron [SQL Server MVP]" wrote:

> That will give you a rough count of what's in the entire table. However,
> the OP said they wanted the count of the resultset... which is likely a
> combination of a join between multiple tables and a filtered where clause.
>
>|||"examnotes" <KH@.discussions.microsoft.com> wrote in message news:<5EC86F57-C7F7-4AC0
-A197-B1C677605F8A@.microsoft.com>...
> Yea, the original question is pretty ambiguious.
> In the scenario you suggest using @.@.ROWCOUNT after the select might work f
or
> this application.
> It all depends eh? ;)
> - KH
>
> "Aaron [SQL Server MVP]" wrote:
>
respond