You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Zhang Jinsheng (JIRA)" <de...@db.apache.org> on 2004/12/24 10:56:03 UTC
[jira] Created: (DERBY-110) performance
performance
-----------
Key: DERBY-110
URL: http://nagoya.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://nagoya.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
[jira] Commented: (DERBY-110) performance
Posted by "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
[jira] Updated: (DERBY-110) hsqldb is faster than derby doing inserts
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-110?page=all ]
Mike Matrigali updated DERBY-110:
---------------------------------
Component: Performance
Summary: hsqldb is faster than derby doing inserts (was: performance)
Description:
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.
was:
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.
Environment:
CPU 2.40GHz
windows 2000
was:
CPU 2.40GHz
windows 2000
> hsqldb is faster than derby doing inserts
> -----------------------------------------
>
> Key: DERBY-110
> URL: http://issues.apache.org/jira/browse/DERBY-110
> Project: Derby
> Type: Test
> Components: Performance
> 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
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-110) performance
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://nagoya.apache.org/jira/browse/DERBY-110?page=comments#action_57035 ]
Sunitha Kambhampati commented on DERBY-110:
-------------------------------------------
1) Check if you are running in autocommit mode false. Inserts can be painfully slow in autocommit mode. The reason is that each commit involves a flush of the log to the disk for each insert statement. The commit will not return until a physical disk write has been executed.There is a jira entry to document this performance tip.
http://nagoya.apache.org/jira/browse/DERBY-108
2) It is not a good idea to use Statement, use PreparedStatement instead. Using prepared statements instead of statements can help avoid unnecessary compilation which saves time.
check the following links in the tuning manual:
http://incubator.apache.org/derby/manuals/tuning/perf21.html#HDRSII-PERF-18705
http://incubator.apache.org/derby/manuals/tuning/perf34.html#IDX438
> performance
> -----------
>
> Key: DERBY-110
> URL: http://nagoya.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://nagoya.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
[jira] Commented: (DERBY-110) performance
Posted by "Gerald Khin (JIRA)" <de...@db.apache.org>.
[ http://nagoya.apache.org/jira/browse/DERBY-110?page=comments#action_57034 ]
Gerald Khin commented on DERBY-110:
-----------------------------------
Did you create an In-Memory table (default in hsqldb) when using hsqldb or cached tables? Did you turn autocommit off when using derby?
> performance
> -----------
>
> Key: DERBY-110
> URL: http://nagoya.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://nagoya.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
[jira] Updated: (DERBY-110) hsqldb is faster than derby doing
inserts
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-110?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-110:
--------------------------------
Issue Type: Improvement (was: Test)
> hsqldb is faster than derby doing inserts
> -----------------------------------------
>
> Key: DERBY-110
> URL: https://issues.apache.org/jira/browse/DERBY-110
> Project: Derby
> Issue Type: Improvement
> Affects 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-110) hsqldb is faster than derby doing inserts
Posted by "Olav Sandstaa (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-110?page=comments#action_12332206 ]
Olav Sandstaa commented on DERBY-110:
-------------------------------------
If you are not concerned with the durability of your transactions or the possibility to recover the database after a database failure, it is possible to try out Derby with the relaxed durability mode:
http://db.apache.org/derby/docs/10.1/tuning/rtunproperdurability.html
This will give Derby a performance that is closer to HSQLDB.
> hsqldb is faster than derby doing inserts
> -----------------------------------------
>
> Key: DERBY-110
> URL: http://issues.apache.org/jira/browse/DERBY-110
> Project: Derby
> Type: Test
> Components: Performance
> 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
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-110) hsqldb is faster than derby doing
inserts
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-110?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-110:
--------------------------------
Derby Categories: [Performance]
> hsqldb is faster than derby doing inserts
> -----------------------------------------
>
> Key: DERBY-110
> URL: https://issues.apache.org/jira/browse/DERBY-110
> Project: Derby
> Issue Type: Test
> Affects 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-110) hsqldb is faster than derby doing inserts
Posted by "Knut Anders Hatlen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-110?page=all ]
Knut Anders Hatlen closed DERBY-110:
------------------------------------
Resolution: Won't Fix
Closing this issue since no one objected to Olav's proposal.
> hsqldb is faster than derby doing inserts
> -----------------------------------------
>
> Key: DERBY-110
> URL: http://issues.apache.org/jira/browse/DERBY-110
> Project: Derby
> Type: Test
> Components: Performance
> 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
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-110) hsqldb is faster than derby doing inserts
Posted by "Olav Sandstaa (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-110?page=comments#action_12332212 ]
Olav Sandstaa commented on DERBY-110:
-------------------------------------
Since Derby is basically designed to log transaction to durable memory/disk, I do not think it is a bug that Derby is unable to compete with a main-memory database like HSQLDB with regards to insert performance. If nobody objects, I propose that we close this JIRA-issue with a status of that we will not fix this problem.
If someone wants to extend Derby to become a main-memory database I think it will be better to open a separate JIRA issue for that.
> hsqldb is faster than derby doing inserts
> -----------------------------------------
>
> Key: DERBY-110
> URL: http://issues.apache.org/jira/browse/DERBY-110
> Project: Derby
> Type: Test
> Components: Performance
> 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
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira