Page History
- 2006-Jul-19 20:55 anonymous
- 2006-Jul-19 16:33 anonymous
- 2006-Jan-17 14:11 anonymous
- 2005-Dec-03 18:22 anonymous
- 2005-Dec-02 14:23 anonymous
- 2005-Dec-02 14:09 anonymous
- 2005-Oct-20 19:56 anonymous
- 2005-Oct-20 19:40 anonymous
- 2005-Oct-20 19:38 anonymous
- 2005-Oct-20 17:27 drh
- 2005-Oct-20 11:21 anonymous
- 2005-Oct-12 17:34 drh
- 2005-Oct-12 17:33 drh
- 2005-Oct-12 10:06 anonymous
- 2005-Oct-12 03:33 anonymous
- 2005-Aug-06 18:18 anonymous
- 2005-Aug-06 14:10 anonymous
- 2005-Aug-04 19:23 drh
Database File
Both database engines keep the entire database (all tables, indices, views, triggers, stored procedures, etc) in a single disk file. At this time we have no hard information comparing the file sizes but we suspect they will be similar. SQLite will have the advantage in the way it stores tables. Firebird will store indices more compactly, on the other hand.
-
One experimenter found that in one very specific (and artificial) case SQLite database files were 10 times smaller than Firebird database files. See below.
-
The previous paragraph used to read: "One experimenter found that
SQLite database files where 10 time smaller than Firebird database files."
But the paragraph was changed to its current wording by an anonymous
poster - presumable a Firebird proponent. If the poster disagrees with
the remark, I think a better recourse would be to provide evidence to the
contrary. The specific test case sited is small, but small is a virtue
when trying to make a point. As it happens, I have heard this same 10x
figure for database size difference from other independent sources. I
have never tried it myself, and am somewhat perplexed to explain how it
could be so. But the reports are consistent. If anyone has any other
data suggesting that Firebird databases sizes are closer to being
competitive with SQLite, then by all means present your findings. But
editing the words of others to distort their meaning helps nobody.
The SQLite database file is cross-platform. You can freely copy an SQLite database from one machine to another and it will still work. Firebird databases, on the other hand, cannot be copied between machines with differing byte orders or alignment restrictions. To move a Firebird database between platforms you have to back it up on the old platform then restore it on the new platform.
Engine Footprint
The complete SQLite library is about 230KiB statically linked. By omitting unused features, the size of SQLite can be reduced to around 170KiB. The size of Firebird is measured in megabytes - size of fbembed.dll is 1,5MB. The size of firebird is reported to be shrinking, but it is still roughly 10 times larger than SQLite and is never expected to be as lightweight.
Features
Both database engines support all the basics of SQL-92: tables, indices, views, triggers, etc. Support in Firebird is more complete. SQLite omits some features like stored procedures or user-defined functions.
Speed
Rumors on the web indicate that the speed of both engines is comparible with a slight advantage to SQLite. We currently have no hard data on the relative speed of the two systems.
Scalability
Firebird scales from an embedded database up to a full enterprise-class client/server database engine and it does not require any changes at the application level. SQLite is designed to be an embedded database only.
Accessibility
The SQLite source code is designed to be readable and accessible. SQLite is designed to be easy to understand and compile. There are about 35K lines of source code in SQLite so a single programmer can easily become familiar with the entire system. Firebird is much larger and more complex. Much more knowledge and dedication is required in order to hack on Firebird.
Administration
SQLite requires no setup files or other administration. You just call sqlite3_open() with the name of a database file and it runs. The same with embedded Firebird except it more complex to initialize. Firebird can use a configuration file in the working directory, but doesn't require it to be present.
Concurrency
SQLite allows multiple programs to be connected to the same database simultaneously. The embedded version of Firebird does not. If you run Firebird in client/server mode, it allows concurrent access with fine-grain locking. But in embedded mode, only one program can connect to the database at a time.
Robert Simpson wrote on the mailing list on 2005-10-12:
I've found that not to be the case at least in the one very simplistic case I tried:
Using the following schema:
CREATE TABLE Foo ([Id] INTEGER NOT NULL PRIMARY KEY)
Inserting 100,000 items into a sqlite and firebird database, then updating all 100,000 with an UPDATE statement, the final database size was:
SQLite (3.2.5) : 819,200 bytes Firebird (1.5.2.4731 embedded) : 8,736,768 bytes -----
IIRC, as of 3.1 SQLite introduced an optimization that would result in the rows of a table consisting only of a single integer primary key column being stored entirely in the index (all SQLite tables have a "rowid" index). That may account for the magnitude of the difference.