Page History
- 2007-Oct-06 16:47 anonymous
- 2006-May-23 09:17 anonymous
- 2006-Mar-05 05:58 anonymous
- 2006-Feb-15 16:20 drh
- 2005-Sep-06 16:39 drh
- 2005-Jun-22 13:06 anonymous
- 2005-Apr-29 08:13 anonymous
- 2005-Apr-29 02:28 anonymous
- 2005-Apr-15 21:19 anonymous
- 2005-Apr-04 22:24 drh
- 2005-Apr-04 22:20 drh
- 2005-Apr-04 22:00 drh
- 2004-Dec-05 21:09 anonymous
- 2004-Nov-05 16:00 anonymous
- 2004-Aug-29 17:10 anonymous
- 2004-Aug-29 16:53 anonymous
- 2004-Aug-14 13:06 drh
- 2004-Aug-04 12:28 anonymous
- 2004-Aug-04 01:18 drh
The information provided on this page was initially written based on a reading of the on-line documentation of Derby. Though the information is believed to be correct, the author is not a Derby expert and may have misunderstood parts of the documentation. If you find errors, you are encouraged to fix them.
Fundamental Differences
The basic idea behind Derby appears to be that it reads the entire database into memory and parses it into various Java data structures. All database I/O occurs on the in-memory image. When the database is closed, all the data is written back to disk. Thus the disk image is really just a serialization of the in-memory database representation.
SQLite, on the other hand, operates directly from disk. It reads only those parts of the database file that it needs in order to carry out the requested operations.
Zero-Administration
Both SQLite and Derby offer zero-administration, embeddable SQL database engines that store all data in a single cross-platform disk file.
Host Language Support
SQLite is written in ANSI-C. It supports bindings to dozens of language, including Java. Derby is only available to applications written in Java.
SQL Language Support
Derby supports all of SQL92. SQLite only supports a subset of SQL92, though the supported subset is large and covers the most commonly used parts of SQL92.
Memory Utilization
The code footprint of SQLite is less than 250KB. The code footprint for Derby is about 2000KB or about 8 time larger.
Derby appears to require that the entire database image be stored in memory. SQLite keeps most the database image on disk and only loads into memory that portion of the database that it is actualy using. One therefore expects that the run-time memory requirements for SQLite will be much smaller.
Concurrency
SQLite allows multiple simultaneous readers and a single writer. Mutiple processes can have the database open simultaneously. Derby only lets a single process open the database at one time.
Crash-Resistance
An SQLite database will survive a program crash or even a power failure. With Derby, if you pull the power plug at the wrong instant, you risk corrupting the database. (Someone please verify this.)
Database File Size
No data is currently available on the relative sizes of the database files for SQLite and Derby. One can guess that since a Derby database is a serialization of internal data structures that it is somewhat smaller than an SQLite database, primarily because the on-disk image of a Derby database does not need to support random access
Speed
No data is currently available on the relative speed of SQLite and Derby database engines. One can guess that after initialization Derby runs faster than SQLite since Derby is operating entirely out of memory. On the other hand, Derby should be very slow to initialize since it must read and parse the entire database file. In the balance, one would expect Derby to be faster in situations where a program opens a database and then use that database extensively for a long period of time, whereas SQLite would be expected to be much faster when the program opens a database, does one or two quick operations, then immediately closes the database again.