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
);
Both files can be downloaded from following links:
Best Answer
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 usingcount(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 ofcount(journal_id)
.Using
count(*)
will just count records, whilecount(journal_id)
will count records wherejournal_id
is not null. Firebird doesn't use thenot null
state of a primary key, so it checks if that column is not null for each record. On my system, changing tocount(*)
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 thecount(*)
(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 unconditionalcount
.