Fastest non-deleted record count

[SteveDingle]

Heya All,

Old one I'm sure but finding I have to have a revisit it. I need the number of records, non-deleted, in a table. Of course because of deleted records, RECCOUNT() isn't an option and because some table have 10's of millions of record (yes close to the 2 gig limit) a SELECT COUNT(*).. is just taking too long.

COUNT/CALCULATE seems to be quicker, about 1/2 the time, but still not as fast as I would like.

I do know indexes on DELETE() would help but I can't guarantee they will be there and I am looking for generic solutions

So, wondering if any of y'all clever geeks out there have any other suggestions



Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[GriffMG]

One tip, you might find it quicker to count the deleted records, as these *might* be a smaller subset or the table - although this will only help with a suitable index.

Are we to assume that you don't have access to the code?

Do the deleted records have any other thing in common, that might be indexed? I have some systems that clear one or more fields at the time of deletion, so I can reuse the deleted records by looking for blank keys (with the SET DELETED appropriately set).



Regards

Griff
Keep [Smile]ing