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 were 10 times smaller than Firebird database files."
But the paragraph was changed to its current wording by an anonymous
poster - presumably 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 I 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.
Other experimenter found that in another very specific (and artificial) case SQLite database files were 17 times larger than Firebird database files. See here: http://sqlite.phxsoftware.com/forums/307/ShowPost.aspx#307 .
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 is 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.
Louis Luangkesorn wrote on 2005-12-2
In the interest of providing one slightly off-topic (Firebird not embedded) data point, I took a text file with 397972 records of 40 fields each, imported it into SQLite, and then used Interbase DataPump to put it into Firebird in Superserver mode (the steps and the order reflect my database skills, which are admittedly not the most sophisticated). The schemas for both the SQLite and the Firebird database were done by hand. The original text file was 138,358 KB, and I did a basic select query with grouping, summary, and sorting. Timing was done by stopwatch. Both were done on the same machine, sequentially (meaning that both were done under the exact same conditions).
File Size Query time
SQLite (3.2.7) : 201,008 KB 11 s
Firebird (1.5.2.4731 Superserver): 165,268 KB 5 s
For the Firebird schema, the IB DataPump reads the SQLite schema creates a Firebird schema with the text fields as memo(), which (my guess) would use more memory than varchar(n) so I redid the schema by hand using varchar(n) with appropriate values of n (since I was lazy, I set n=10 or n= 70 depending on whether the field was large or small).
Some better real-world benchmarks can be found here: