How to speed up Count(*) in Interbase/Firebird

Interbase is a generational database.

That's great because rollbacks are near instantaneous, but count(*) takes forever.

This is u开发者_JAVA百科nlike e.g. MySQL where count can use an index.

I never knew why until I saw this:

Even when an index is available on the column or columns included in the COUNT, all records must be visited in order to see if they are visible under the current transaction isolation.

On wikipedia: http://en.wikipedia.org/wiki/InterBase

Any tips on how to do fast counting in Interbase/Firebird

According to this link: http://www.firebirdfaq.org/faq5/

There is another solution. This one is by Ivan Prenosil, a long time Interbase and Firebird hacker. This solution only returns an approximate record count. As Ann W. Harrison kindly explains: Any record that has had its primary key modified will appear twice if the old version has not been garbage collected and deleted records will continue in the count until they are garbage collected.

/* first update the statistics */
UPDATE RDB$INDICES SET RDB$STATISTICS = -1;
COMMIT;

/* Display table names and record counts */
SELECT RDB$RELATIONS.RDB$RELATION_NAME,
CASE 
WHEN RDB$INDICES.RDB$STATISTICS = 0 THEN 0 
ELSE CAST(1 / RDB$INDICES.RDB$STATISTICS AS INTEGER) 
END 
FROM RDB$RELATIONS 
LEFT JOIN RDB$RELATION_CONSTRAINTS 
 ON RDB$RELATIONS.RDB$RELATION_NAME = RDB$RELATION_CONSTRAINTS.RDB$RELATION_NAME 
 AND RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN RDB$INDICES 
  ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDICES.RDB$INDEX_NAME 
WHERE RDB$VIEW_BLR IS NULL AND RDB$RELATION_ID >= 128 
ORDER BY 1;

This will only work on tables that have a primary key.

You could also make 2 triggers for the table you want to count. This solution is intended for some special tables where you have a lot of "action".

CREATE TRIGGER TABLE_BI0 ACTIVE BEFORE INSERT
BEGIN
   UPDATE COUNTING_TABLE
   SET LINES=LINES + 1
   WHERE TABLE='TABLE_NAME'; /* Table name here*/
END


CREATE TRIGGER TABLE_BD0 ACTIVE BEFORE DELETE
BEGIN
   UPDATE COUNTING_TABLE
   SET LINES=LINES - 1
   WHERE TABLE='TABLE_NAME'; /* Table name here*/
END

After that, when you need to know the count of that special table, you just select it from COUNTING_TABLE

SELECT LINES FROM COUNTING_TABLE
WHERE TABLE='TABLE_NAME' /* Table name here*/