[jira] Commented: (DERBY-110) performance

["Sunitha Kambhampati (JIRA)" <de...@db.apache.org>]

     [ http://issues.apache.org/jira/browse/DERBY-110?page=comments#action_58011 ]
     
Sunitha Kambhampati commented on DERBY-110:
-------------------------------------------

I believe that it is not a fair apples to apples comparison to be comparing HSQLDB and Apache Derby (aka Cloudscape).  There are some important differences in the functionality provided by HSQLDB and Derby which in turn contribute to how they perform.

1) Transactions:
HSQLDB does *not* support transaction isolation. All transactions run in read uncommitted ( dirty read mode). Transactions read dirty data ( uncommitted data) which is not what you want in case of update transactions/ lets say a banking application like tpc-b. 

Derby supports *all* transaction isolation levels. The default tranaction isolation that it runs in is READ_COMMITTED. Derby is guaranteeing that if you run in read committed mode, you will not be reading dirty data.  

2) In-memory database:
HSQLDB by default creates table in memory , so if you use CREATE TABLE the table is in memory. This means with large amounts of data there is high memory utilization and the application may be limited to the amount of memory available and may perform slow if table does not fit in memory.

http://www.jboss.org/wiki/Wiki.jsp?page=ConfigJBossMQDB talks about out of memory errors as a result. 
http://www.devx.com/IBMCloudscape/Article/21773 this article talks about how this could lead to scalability issues 

Derby is disk based. Derby uses a page cache to keep recently used pages in memory and writes data to disk. Thus the memory consumption is stable and can be used for large amounts of data.

This difference is important to note as the speed in these 2 cases are different. This seems to be reason why it is not ideal to compare speed differences here. 

3) Reliability:

Derby is guaranteeing that if your system crashes in any way that committed transactions will remain committed. This requires that when a transaction commits, logs are synced to the disk.  Syncing to the disk takes time. 

But on the other hand, it seems like hsqldb is not failsafe as the log file is not flushed (synced) to the disk after a commit.  

http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby-user@db.apache.org&msgNo=11
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby-user@db.apache.org&msgNo=13 gives a little more detail on the log flusher thread for hsqldb.

Thus it seems necessary to consider these differences before one compares raw numbers.

Some links from web on hsqldb and derby:
http://forums.atlassian.com/thread.jspa?threadID=6153&messageID=248904679
http://developers.slashdot.org/comments.pl?sid=127289&threshold=1&commentsort=0&tid=221&tid=198&tid=136&tid=108&tid=8&tid=2&mode=thread&pid=10640524#10642178
http://www.luisdelarosa.com/blog/2004/10/whatever_happen.html
post by stephane TRAUMAT
http://www.jboss.org/wiki/Wiki.jsp?page=ConfigJBossMQDB

> performance
> -----------
>
>          Key: DERBY-110
>          URL: http://issues.apache.org/jira/browse/DERBY-110
>      Project: Derby
>         Type: Test
>     Versions: 10.0.2.1
>  Environment: CPU 2.40GHz
> windows 2000
>     Reporter: Zhang Jinsheng
>     Priority: Minor

>
> 1. create db name systable in derby and hsqldb (another open source dbms);
> 2. create table named a table ('CREATE TABLE aTable(id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, description VARCHAR(255))');
> 3. insert ten thousands row in table "atable"
>     for(int i=1; i<10000; i++) {
> 	sql = "INSERT INTO aTable VALUES("+i+", 'haha', 'zhang'' test')";
> 	stmt.execute(sql);
> 	System.out.println(i);
>     }
> 4. derby spend 50390 millisecond;
>    hsqldb spend 4250 millisecond;
>  
> 5. conclusion: hsqldb has more perfect performance.
>    Maybe derby need to improve it's performance.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira