db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <kar...@Source-Zone.Org>
Subject Re: [jira] Commented: (DERBY-110) performance
Date Mon, 27 Dec 2004 18:18:04 GMT
More info...

The following is from the HSQLDB FAQ  site at SourceForge:


1) If only memory tables (CREATE TABLE or CREATE MEMORY TABLE) are used 
then the database is limited by the memory. A minimum of bout 100 bytes 
plus the actual data size are required for each row. If you use CREATE 
CACHED TABLE, then the size of the table is not limited by the memory 
beyond a certain minimum size. The data and indexes of cached tables are 
saved to disk. With text tables, indexes are memory resident but the 
data is cached to disk.

2) If data has been added to CACHED tables then the database was not 
shut down properly. When you restart the database, the *.log file will 
be processed and an automatic checkpoint will be performed. No commited 
data will be lost.

The statements that make up the database are saved in the *.script file 
(mostly CREATE statements and INSERT statements for memory tables). Only 
data of cached tables (CREATE CACHED TABLE) are stored in the *.data 
file. Also all data manipulation operations are stored in the *.log file 
(mostly DELETE/INSERT) for crash recovery. When the SHUTDOWN or 
CHECKPOINT command is issued to a database, then the *.script file is 
re-created and becomes up-to-date. The .log file is deleted. The *.data 
file is also closed and backed up as the *.backup file. When the 
database is restarted, all statements of the *.script file are executed 
first and new statements are appended to the .log file as the database 
is used.

In short:

The default 'CREATE TABLE' is by default a memory table and  _only the 
DDL/DML_ gets written to a '.script' file !!
It is the 'CREATE CACHED TABLE' that will create the .dat files to store 
the data for such tables in the database.

Hope the above info helps in your comparision.


Daniel John Debrunner wrote:

>Hash: SHA1
>zhangjinsheng wrote:
>>Both hsqldb and derby are embedded, I haven't do any settings (all are in
>With your test Derby and hsqlb are not performing the same
>functionality, thus you have to consider if this is a reasonable
>performance test.
>Each INSERT statement is a seprate transaction due to auto commit mode,
>auto commit is on by default as defined by the JDBC spec.
>Derby is guaranteeing that if the system crashes in any way that any
>committed transactions will remain committed, in this case each insert
>statement has an implicit commit. This guarantee requires a forced write
>to the disk for every commit, this is where Derby is spending most of
>its time, waiting for the data to be flushed to disk.
>I do not belive hsqldb is providing this guarantee, thus it goes faster
>because it is not waiting for the disk after each statement.
>This thread has some info on the differences between Derby and hsqldb.
>Version: GnuPG v1.2.5 (MingW32)
>Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

View raw message