Firebird COUNT is very slow compared to SQLite

I am using Firebird for past few months and today a customer complained about performance issue.

I checked into client's database and they had 167,77,216 rows in one table. So I made a clone of that table into another empty database and loaded with dummy records and tested with a simple query

select count(journal_id) from acc_journal

It took 14.28 seconds.

After that I made a SQLite database and loaded with this data and it took 2.216 seconds.

Is this Firebid's nature or I am missing something?

Firebird table structure:

CREATE TABLE ACC_JOURNAL (
  JOURNAL_ID       INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACCOUNT_HEAD_ID  INTEGER,
  REF_ID           INTEGER,
  REF_TYPE         VARCHAR(20),
  DESCRIPTION      VARCHAR(512),
  DEBIT            DECIMAL(18,4),
  DEBIT_EX         DECIMAL(18,4),
  CREDIT           DECIMAL(18,4),
  CREDIT_EX        DECIMAL(18,4),
  CURRENCY_ID      INTEGER,
  STATUS           VARCHAR(20),
  EX_RATE          DECIMAL(18,4),
  ENTRY_DATE       VARCHAR(20),
  ENTRY_TIME       VARCHAR(20),
  BRANCH_CODE      VARCHAR(20),
  LAST_SYNCED      VARCHAR(20),
  LAST_UPDATED     DECIMAL(18,4),
  ENTRY_DATE2      VARCHAR(20),
  /* Keys */
  PRIMARY KEY (JOURNAL_ID)
);

SQLite table structure:

CREATE TABLE ACC_JOURNAL (
    ACCOUNT_HEAD_ID INTEGER,
    REF_ID          INTEGER,
    REF_TYPE        VARCHAR (20),
    DESCRIPTION     VARCHAR (512),
    DEBIT           DECIMAL (18, 4),
    DEBIT_EX        DECIMAL (18, 4),
    CREDIT          DECIMAL (18, 4),
    CREDIT_EX       DECIMAL (18, 4),
    CURRENCY_ID     INTEGER,
    STATUS          VARCHAR (20),
    EX_RATE         DECIMAL (18, 4),
    ENTRY_DATE      VARCHAR (20),
    ENTRY_TIME      VARCHAR (20),
    BRANCH_CODE     VARCHAR (20),
    LAST_SYNCED     VARCHAR (20),
    LAST_UPDATED    DECIMAL (18, 4),
    ENTRY_DATE2     VARCHAR (20),
    JOURNAL_ID      INTEGER         PRIMARY KEY AUTOINCREMENT
);

Unfortunately, this is due to the fact that Firebird has to do a full table scan to get the count. I'm not familiar with how SQLite works, but a quick search seems to indicate that SQLite can use an index to get a count (eg primary key index for count(*) or an index on a column if using count(columnname), though I haven't verified if this is actually correct).

The reason Firebird needs to do a full table scan, is because Firebird is a MVCC (Multi-Version Concurrency Control) database, but record visibility information is not included in its indexes. So, to be able to know that a record is visible to your transaction and therefor should be included in the count, Firebird needs to read the actual record. And because the query is an unconditional count, using an index and then checking each record would be slower than just reading each record.

However, there are some things you can do to improve performance:

-
Use count(*) instead of count(journal_id).

Using count() will just count records, while count(journal_id) will count records where journal_id is not null. Firebird doesn't use the not null state of a primary key, so it checks if that column is not null for each record. On my system, changing to count() reduces the query time by around 25%.

-
Use a larger page size.

Your example database has a page size of 4096, increasing it to a page size of 16384 reduces the execution time your example query (with count(journal_id)) by around 20%, while the count(*) (compared to your original query on page size 4096) is reduced by more than 50%.

However, given the overall poor performance of an unconditional count, the primary advice would be to avoid having to use unconditional count.

StackExchange Database Administrators Q#253160, answer score: 5