Faster count method using sysindexes on complex query

Faster count method using sysindexes on complex query

Post by Nicolas Verhaegh » Wed, 21 Aug 2002 01:39:05



The following statement:

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('my_table') AND indid < 2

will be a lot faster than this statement:

SELECT count(*)
FROM my_table

Now, is it possible to adapt this solution with a more complex count, based
on field
content and even in a relational model?

I was thinking about a temp table in a stored procedure, but it might not
really be a good idea.

Thanks for any input!

 
 
 

Faster count method using sysindexes on complex query

Post by Andrew J. Kell » Wed, 21 Aug 2002 02:38:40


No it's not and although this may be faster it is also not always accurate.
If you want a truly accurate count then you have to use COUNT(*).

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:> The following statement:

> SELECT rows
> FROM sysindexes
> WHERE id = OBJECT_ID('my_table') AND indid < 2

> will be a lot faster than this statement:

> SELECT count(*)
> FROM my_table

> Now, is it possible to adapt this solution with a more complex count,
based
> on field
> content and even in a relational model?

> I was thinking about a temp table in a stored procedure, but it might not
> really be a good idea.

> Thanks for any input!


 
 
 

1. Faster count using sysindexes in a relational model

The following statement:

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('my_table') AND indid < 2

will be a lot faster than this statement:

SELECT count(*)
FROM my_table

Now, is it adapt this solution with a more complex count, based on field
content and even in a relational model?

Thanks for any input!

2. Embedded SQL for C Toolkit

3. Problem with complex sysindexes and complex stored procedures

4. Help on CURSOR

5. What is the fastest ADO record counting method?

6. problem to connect with a a servlet and jdbc to orable

7. Fast Methods for Query ???

8. Creating table dynamically

9. Reading records using MFC CDaoRecordSet (fast method required!)

10. Fast table count or table block count needed.

11. Advice on indexes for complex Select query with very complex Where clause

12. Incorrect row count from sysindexes

13. row count in sysindexes