Welcome to the world of client/server, Yeoh - or maybe it is only
Firebird/Interbase (I have no experience with other c/s databases). Unlike
desktop databases IB/FB have nowhere to store the number of records (the
result will vary depending on when the transaction counting the records
were started) and have to access every record in the result set to count
them every time you issue SELECT COUNT. And accessing over 1m records in 20
seconds is not what I call slow.

Either do as Kenneth recommends (beware of it being a potential bottleneck
if your database has lots of inserts/deletes) or be more careful about
issueing SELECT COUNT. Normally, SELECT COUNT on a million records is not
required and when it is (e.g. for monthly or yearly reports), convince your
users that it is a massive calculation that does require some time.

Set