Count query takes too long time

[shabar]

Hi All

I have query (view) with lots of records and there I need to get a count. when I ran below query it takes lots of time but didn't get a result

SELECT count(*)
FROM Table1

What could be the reason



Cheers



Shabar

[NTC]

define "lots of records" ...?? XP doesn't typically have gobs of RAM so possibly the record count really is excessive compared to the hardware.....

but typically you need just the key field in the query (not all fields) if you are simply counting the table records...a simple aggregate query generally runs pretty fast.

[shabar]

Hi NTC

Thax for your quick reply

Just a question currently I don't have primary key in my query. how to create primary key in select query. This is the saved query that I use to get the record count


Cheers

shabar

[NTC]

the key field exists in the table. it isn't created in the query. I suggested using the key field to count because there is always a value in every record where as some other field there may perhaps be nulls such that the count would not be of every record. If there is any field where you absolutely know there is always a value - then you can count that field alone.

when I set up a count in an aggregate query, I select just 1 field to count. generally it is quite fast. I don't think counting just 1 field vs counting the whole record explains a significant time difference all things being equal; but when the table's record count approaches 1M or even 500k then older hardware / XP can go into never never land when the query requires alot of RAM....

if your aggregate query is of another query rather than a table - then generally that explains why it is slow or going into never never land. in which case consider tweaking that underlying query to make it more efficient....strip out fields and remove any sorting....

[shabar]

Thax mate

cheers