[sip-comm-dev] GSoC project – Storing chat history in a database


#1

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s Weblog
http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,

HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (
http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are most
appreciated.

Regards

Ajay

PolePosition.pdf (275 KB)


#2

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

···

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s Weblog
http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (
http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#3

Hi Ajay, All,

After reading all the links and documents you are pointing here, I do
agree with you. HSQLDB seems very performant on common use cases and
easy to integrate. It seems that the OpenOffice team has also chose it
so they perhaps came to the same conclusions as us.

I'd just like to point out one possible bottleneck: as this DB is
buffering most of the data when a connection is created, the launch
time could be long so we may have to create some sort of request
buffering while starting the DB service in background, but we will see
that when implementing the service.

I'm really not a DB specialist, is there here anyone that is more
familiar with those DB systems and that can give us another point of
view on that?

Cheers,

Ben

···

On Wed, May 13, 2009 at 20:48, AJAY CHHATWAL <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s Weblog
http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (
http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#4

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how it
compares to other popular projects such as Java DB/Derby, and the Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

···

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL > <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s Weblog
http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (
http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX: +33.1.77.62.47.31
http://sip-communicator.org PHONE: +33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#5

Hi Emil,

Here is a brief comparison of the features of the popular java database engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

···

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov <emcho@sip-communicator.org> wrote:

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how it
compares to other popular projects such as Java DB/Derby, and the Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s Weblog
http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (
http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX: +33.1.77.62.47.31
http://sip-communicator.org PHONE: +33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#6

Hi Emil,all

I have also found some more info on comparisons b/w H2,HSQLDB,Derby and Sqlite.
Plz see http://www.jepoirrier.net/blog/2007/09/sqlitejdbc-worst-than-derby/

Regards,
Ajay

···

On Sun, May 17, 2009 at 2:08 PM, AJAY CHHATWAL <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,

Here is a brief comparison of the features of the popular java database engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov <emcho@sip-communicator.org> wrote:

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how it
compares to other popular projects such as Java DB/Derby, and the Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s Weblog
http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (
http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX: +33.1.77.62.47.31
http://sip-communicator.org PHONE: +33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#7

Sorry,forgot to include all the links

http://www.jepoirrier.net/blogimages/070917-db.html#jrat
http://www.jepoirrier.net/blog/2007/09/more-on-java-dbs-comparison/

···

On Sun, May 17, 2009 at 2:50 PM, AJAY CHHATWAL <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,all

I have also found some more info on comparisons b/w H2,HSQLDB,Derby and Sqlite.
Plz see http://www.jepoirrier.net/blog/2007/09/sqlitejdbc-worst-than-derby/

Regards,
Ajay

On Sun, May 17, 2009 at 2:08 PM, AJAY CHHATWAL > <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,

Here is a brief comparison of the features of the popular java database engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov <emcho@sip-communicator.org> wrote:

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how it
compares to other popular projects such as Java DB/Derby, and the Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s Weblog
http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (
http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX: +33.1.77.62.47.31
http://sip-communicator.org PHONE: +33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#8

Hi Ajay,

I saw your report about HSQLDB and other features and performance comparison.
But I'm wondering about something else. I have seen that actually hsqldb keeps the data as something as sql script (text file with all create and insert statements). So here are my thoughts :
- what is the startup time of the db engine, as it has to create all tables and insert all records before using them ?
- what is the memory usage on larger tables ?

And here is a pseudo program for testing this. In my opinion it will answer the questions above:

print memory ussage 1;
long firstTime = ....
  init what is needed for the db
  get the connection

print memory ussage 2;
print control time 1 (secondTime = currentTime - firstTime);
  select from table A row with id = 1;

print memory ussage 3;
print control time 2 (currentTime - secondTime);

Run this program in two scenarios : 1. Have a table A with one record in it with id = 1;
2. Have a table A with one thousand records in it with id = 1;
Or more records if you want so we can have a better look how time increases when record number increases.

What do you think ?

Cheers
damencho

AJAY CHHATWAL wrote:

···

Sorry,forgot to include all the links

http://www.jepoirrier.net/blogimages/070917-db.html#jrat
http://www.jepoirrier.net/blog/2007/09/more-on-java-dbs-comparison/

On Sun, May 17, 2009 at 2:50 PM, AJAY CHHATWAL > <ajay.chhatwal.cse07@itbhu.ac.in> wrote:
  

Hi Emil,all

I have also found some more info on comparisons b/w H2,HSQLDB,Derby and Sqlite.
Plz see http://www.jepoirrier.net/blog/2007/09/sqlitejdbc-worst-than-derby/

Regards,
Ajay

On Sun, May 17, 2009 at 2:08 PM, AJAY CHHATWAL >> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:
    

Hi Emil,

Here is a brief comparison of the features of the popular java database engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov <emcho@sip-communicator.org> wrote:
      

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how it
compares to other popular projects such as Java DB/Derby, and the Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:
        

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:
          

Hi all,

I am Ajay Chhatwal and am working on my GSoC project � Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational database
would be more suited the application than an object based DB .

Selection criteria:

� Should be free and open source with compatible license
� Should be reliable
� Should support JDBC and act as a relational database
� INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
� Good documentation
� Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie�s Weblog
http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn�t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn�t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (
http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX: +33.1.77.62.47.31
http://sip-communicator.org PHONE: +33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#9

Hi damencho,

I have just completed testing as desired by you.
Although i do not claim that i performed performance benchmarking,I
also measured the memory usage as well as time taken for startup and
shutdown times and for table creation,insertion and selection for
current stable versions of HSQLDB,H2 and Derby for tables containing
the following no. of rows:
1
10
100
1000
10000
100000
500000
1000000

I have attached the results in the form of 6 text files generated by
my program with the name convention <DBNAME><s>.txt OR <DBNAME><c>.txt
Here,c denotes the results when the tables are first being created
while s denotes the results when records are being selected.

The <DBNAME>s.txt results are important as they measure the startup
times and shutdown after database creation.
(See DERBYs.txt,H2s.txt and HSQLs.txt)

Based on analysis of these files,I came to the following conclusions :

Conclusions:

1. Startup time is almost constant for HSQLDB and H2 while it
increases with table size for derby.

This is because in CACHED tables used for HSQLDB and H2,the data is
stored directly in the .data files and not as .script files. This is
indicated at one point in the documentation(
http://hsqldb.org/web/hsqlDocsFrame.html )
The data is stored as .script files for memory tables which are
anyways also limited by the total memory of the JVM and thus not
useful to our application.

About Derby's Behaviour, it is rather unexpected and I am not able to
understand the reason.

2.The shutdown (closing) time for HSQLDB increases with increase with
size of the table and is quite substantial compared to others.The
shutdown times for H2 also increases but very slowly and is quite less
compared to HSQLDB. The shutdown time for derby is negligible for
derby and almost constant.

This happens because HSQLDB backs the database at every proper shutdown.

3.The memory usage for derby is lower compared to H2 and HSQLDB . H2
and HSQLDB show similar usage till the Database becomes very
big(~500000 rows) at which H2 uses lesser memory

This is probably because of the caching behavior of the three DBMS

4.Derby is extreamely slow (by orders of magnitude) compared to H2 and HSQLDB.
In fact it was so slow that after 1 run took 28 minutes,i didnt test
for larger tables
   H2 and HSQLDB are somewhat comparable in performance but even if we
add the shutdown times,HSQLDB is quite faster than H2.Of course since
the queries tested here are trival,we cant really comment on both
these but it is clean that derby is way behind in terms of
performance.

5.The disk space occupied by the databases is the largest for H2 and
around half of that for HSQLDB and around 1.5 times of HSQLDB for
Derby.

I have attached the source code and the results with this mail.The
source contains a windows batch file for running the tests...It should
also run with minor changes on other platforms (*nix) as it only
contains runs javac and java

Regarding memory usage comparision,use can also see
http://www.h2database.com/html/performance.html
This is probably more accurate compared to my test

If you have any other queries please do contact me.

Regards

Ajay

HSQLs.txt (1.11 KB)

H2s.txt (1.28 KB)

DERBYs.txt (974 Bytes)

HSQLc.txt (1.54 KB)

H2c.txt (1.53 KB)

DERBYc.txt (1.16 KB)

···

On Mon, May 18, 2009 at 1:57 PM, Damian Minkov <damencho@sip-communicator.org> wrote:

Hi Ajay,

I saw your report about HSQLDB and other features and performance
comparison.
But I'm wondering about something else. I have seen that actually hsqldb
keeps the data as something as sql script (text file with all create and
insert statements). So here are my thoughts :
- what is the startup time of the db engine, as it has to create all tables
and insert all records before using them ?
- what is the memory usage on larger tables ?

And here is a pseudo program for testing this. In my opinion it will answer
the questions above:

print memory ussage 1;
long firstTime = ....
       init what is needed for the db
       get the connection

print memory ussage 2;
print control time 1 (secondTime = currentTime - firstTime);
       select from table A row with id = 1;

print memory ussage 3;
print control time 2 (currentTime - secondTime);

Run this program in two scenarios : 1. Have a table A with one record in it
with id = 1;
2. Have a table A with one thousand records in it with id = 1;
Or more records if you want so we can have a better look how time increases
when record number increases.

What do you think ?

Cheers
damencho

AJAY CHHATWAL wrote:

Sorry,forgot to include all the links

http://www.jepoirrier.net/blogimages/070917-db.html#jrat
http://www.jepoirrier.net/blog/2007/09/more-on-java-dbs-comparison/

On Sun, May 17, 2009 at 2:50 PM, AJAY CHHATWAL >> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,all

I have also found some more info on comparisons b/w H2,HSQLDB,Derby and
Sqlite.
Plz see
http://www.jepoirrier.net/blog/2007/09/sqlitejdbc-worst-than-derby/

Regards,
Ajay

On Sun, May 17, 2009 at 2:08 PM, AJAY CHHATWAL >>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,

Here is a brief comparison of the features of the popular java database
engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov <emcho@sip-communicator.org> >>>> wrote:

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how it
compares to other popular projects such as Java DB/Derby, and the Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational
database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s Weblog

http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source
license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer
community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following
reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (

http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are
most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail:
dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX: +33.1.77.62.47.31
http://sip-communicator.org PHONE: +33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#10

Hi damencho,

This attachment is causing problems in being sent .... so i am sending
it separately

Regards,

Ajay

TestDB.zip (4.12 MB)

···

On Mon, May 18, 2009 at 11:09 PM, AJAY CHHATWAL <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi damencho,

I have just completed testing as desired by you.
Although i do not claim that i performed performance benchmarking,I
also measured the memory usage as well as time taken for startup and
shutdown times and for table creation,insertion and selection for
current stable versions of HSQLDB,H2 and Derby for tables containing
the following no. of rows:
1
10
100
1000
10000
100000
500000
1000000

I have attached the results in the form of 6 text files generated by
my program with the name convention <DBNAME><s>.txt OR <DBNAME><c>.txt
Here,c denotes the results when the tables are first being created
while s denotes the results when records are being selected.

The <DBNAME>s.txt results are important as they measure the startup
times and shutdown after database creation.
(See DERBYs.txt,H2s.txt and HSQLs.txt)

Based on analysis of these files,I came to the following conclusions :

Conclusions:

1. Startup time is almost constant for HSQLDB and H2 while it
increases with table size for derby.

This is because in CACHED tables used for HSQLDB and H2,the data is
stored directly in the .data files and not as .script files. This is
indicated at one point in the documentation(
http://hsqldb.org/web/hsqlDocsFrame.html )
The data is stored as .script files for memory tables which are
anyways also limited by the total memory of the JVM and thus not
useful to our application.

About Derby's Behaviour, it is rather unexpected and I am not able to
understand the reason.

2.The shutdown (closing) time for HSQLDB increases with increase with
size of the table and is quite substantial compared to others.The
shutdown times for H2 also increases but very slowly and is quite less
compared to HSQLDB. The shutdown time for derby is negligible for
derby and almost constant.

This happens because HSQLDB backs the database at every proper shutdown.

3.The memory usage for derby is lower compared to H2 and HSQLDB . H2
and HSQLDB show similar usage till the Database becomes very
big(~500000 rows) at which H2 uses lesser memory

This is probably because of the caching behavior of the three DBMS

4.Derby is extreamely slow (by orders of magnitude) compared to H2 and HSQLDB.
In fact it was so slow that after 1 run took 28 minutes,i didnt test
for larger tables
H2 and HSQLDB are somewhat comparable in performance but even if we
add the shutdown times,HSQLDB is quite faster than H2.Of course since
the queries tested here are trival,we cant really comment on both
these but it is clean that derby is way behind in terms of
performance.

5.The disk space occupied by the databases is the largest for H2 and
around half of that for HSQLDB and around 1.5 times of HSQLDB for
Derby.

I have attached the source code and the results with this mail.The
source contains a windows batch file for running the tests...It should
also run with minor changes on other platforms (*nix) as it only
contains runs javac and java

Regarding memory usage comparision,use can also see
http://www.h2database.com/html/performance.html
This is probably more accurate compared to my test

If you have any other queries please do contact me.

Regards

Ajay

On Mon, May 18, 2009 at 1:57 PM, Damian Minkov > <damencho@sip-communicator.org> wrote:

Hi Ajay,

I saw your report about HSQLDB and other features and performance
comparison.
But I'm wondering about something else. I have seen that actually hsqldb
keeps the data as something as sql script (text file with all create and
insert statements). So here are my thoughts :
- what is the startup time of the db engine, as it has to create all tables
and insert all records before using them ?
- what is the memory usage on larger tables ?

And here is a pseudo program for testing this. In my opinion it will answer
the questions above:

print memory ussage 1;
long firstTime = ....
init what is needed for the db
get the connection

print memory ussage 2;
print control time 1 (secondTime = currentTime - firstTime);
select from table A row with id = 1;

print memory ussage 3;
print control time 2 (currentTime - secondTime);

Run this program in two scenarios : 1. Have a table A with one record in it
with id = 1;
2. Have a table A with one thousand records in it with id = 1;
Or more records if you want so we can have a better look how time increases
when record number increases.

What do you think ?

Cheers
damencho

AJAY CHHATWAL wrote:

Sorry,forgot to include all the links

http://www.jepoirrier.net/blogimages/070917-db.html#jrat
http://www.jepoirrier.net/blog/2007/09/more-on-java-dbs-comparison/

On Sun, May 17, 2009 at 2:50 PM, AJAY CHHATWAL >>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,all

I have also found some more info on comparisons b/w H2,HSQLDB,Derby and
Sqlite.
Plz see
http://www.jepoirrier.net/blog/2007/09/sqlitejdbc-worst-than-derby/

Regards,
Ajay

On Sun, May 17, 2009 at 2:08 PM, AJAY CHHATWAL >>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,

Here is a brief comparison of the features of the popular java database
engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov <emcho@sip-communicator.org> >>>>> wrote:

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how it
compares to other popular projects such as Java DB/Derby, and the Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >>>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational
database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s Weblog

http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source
license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer
community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following
reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (

http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are
most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail:
dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX: +33.1.77.62.47.31
http://sip-communicator.org PHONE: +33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#11

Hi again,

first of all thank you for the detailed tests and reports. I thought that HSQL will import the data when started which will cause slow startup and high memory usage, but as I see this is not the case.
As you pointed maybe its due to some caching mechanism they got.
I also had a look at a link you pointed about performance comparison between Embedded databases and in there you can see that HSQL is significantly slower than H2 with some queries and uses twice more momory, there they are talking about bad query optimizer but the test was with more than 500K queries, which I think is also not our case.
So after your tests and comparing the sizes of the libs (h2 is twice the size of hsql) and that openoffice are also using it - for me the choice is hsql.

Cheers
damencho

AJAY CHHATWAL wrote:

···

Hi damencho,

This attachment is causing problems in being sent .... so i am sending
it separately

Regards,

Ajay

On Mon, May 18, 2009 at 11:09 PM, AJAY CHHATWAL > <ajay.chhatwal.cse07@itbhu.ac.in> wrote:
  

Hi damencho,

I have just completed testing as desired by you.
Although i do not claim that i performed performance benchmarking,I
also measured the memory usage as well as time taken for startup and
shutdown times and for table creation,insertion and selection for
current stable versions of HSQLDB,H2 and Derby for tables containing
the following no. of rows:
1
10
100
1000
10000
100000
500000
1000000

I have attached the results in the form of 6 text files generated by
my program with the name convention <DBNAME><s>.txt OR <DBNAME><c>.txt
Here,c denotes the results when the tables are first being created
while s denotes the results when records are being selected.

The <DBNAME>s.txt results are important as they measure the startup
times and shutdown after database creation.
(See DERBYs.txt,H2s.txt and HSQLs.txt)

Based on analysis of these files,I came to the following conclusions :

Conclusions:

1. Startup time is almost constant for HSQLDB and H2 while it
increases with table size for derby.

This is because in CACHED tables used for HSQLDB and H2,the data is
stored directly in the .data files and not as .script files. This is
indicated at one point in the documentation(
http://hsqldb.org/web/hsqlDocsFrame.html )
The data is stored as .script files for memory tables which are
anyways also limited by the total memory of the JVM and thus not
useful to our application.

About Derby's Behaviour, it is rather unexpected and I am not able to
understand the reason.

2.The shutdown (closing) time for HSQLDB increases with increase with
size of the table and is quite substantial compared to others.The
shutdown times for H2 also increases but very slowly and is quite less
compared to HSQLDB. The shutdown time for derby is negligible for
derby and almost constant.

This happens because HSQLDB backs the database at every proper shutdown.

3.The memory usage for derby is lower compared to H2 and HSQLDB . H2
and HSQLDB show similar usage till the Database becomes very
big(~500000 rows) at which H2 uses lesser memory

This is probably because of the caching behavior of the three DBMS

4.Derby is extreamely slow (by orders of magnitude) compared to H2 and HSQLDB.
In fact it was so slow that after 1 run took 28 minutes,i didnt test
for larger tables
  H2 and HSQLDB are somewhat comparable in performance but even if we
add the shutdown times,HSQLDB is quite faster than H2.Of course since
the queries tested here are trival,we cant really comment on both
these but it is clean that derby is way behind in terms of
performance.

5.The disk space occupied by the databases is the largest for H2 and
around half of that for HSQLDB and around 1.5 times of HSQLDB for
Derby.

I have attached the source code and the results with this mail.The
source contains a windows batch file for running the tests...It should
also run with minor changes on other platforms (*nix) as it only
contains runs javac and java

Regarding memory usage comparision,use can also see
http://www.h2database.com/html/performance.html
This is probably more accurate compared to my test

If you have any other queries please do contact me.

Regards

Ajay

On Mon, May 18, 2009 at 1:57 PM, Damian Minkov >> <damencho@sip-communicator.org> wrote:
    

Hi Ajay,

I saw your report about HSQLDB and other features and performance
comparison.
But I'm wondering about something else. I have seen that actually hsqldb
keeps the data as something as sql script (text file with all create and
insert statements). So here are my thoughts :
- what is the startup time of the db engine, as it has to create all tables
and insert all records before using them ?
- what is the memory usage on larger tables ?

And here is a pseudo program for testing this. In my opinion it will answer
the questions above:

print memory ussage 1;
long firstTime = ....
       init what is needed for the db
       get the connection

print memory ussage 2;
print control time 1 (secondTime = currentTime - firstTime);
       select from table A row with id = 1;

print memory ussage 3;
print control time 2 (currentTime - secondTime);

Run this program in two scenarios : 1. Have a table A with one record in it
with id = 1;
2. Have a table A with one thousand records in it with id = 1;
Or more records if you want so we can have a better look how time increases
when record number increases.

What do you think ?

Cheers
damencho

AJAY CHHATWAL wrote:
      

Sorry,forgot to include all the links

http://www.jepoirrier.net/blogimages/070917-db.html#jrat
http://www.jepoirrier.net/blog/2007/09/more-on-java-dbs-comparison/

On Sun, May 17, 2009 at 2:50 PM, AJAY CHHATWAL >>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,all

I have also found some more info on comparisons b/w H2,HSQLDB,Derby and
Sqlite.
Plz see
http://www.jepoirrier.net/blog/2007/09/sqlitejdbc-worst-than-derby/

Regards,
Ajay

On Sun, May 17, 2009 at 2:08 PM, AJAY CHHATWAL >>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,

Here is a brief comparison of the features of the popular java database
engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov <emcho@sip-communicator.org> >>>>>> wrote:

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how it
compares to other popular projects such as Java DB/Derby, and the Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >>>>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project � Storing chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the History
records stored in the DB using various criteria, a relational
database
would be more suited the application than an object based DB .

Selection criteria:

� Should be free and open source with compatible license
� Should be reliable
� Should support JDBC and act as a relational database
� INSERT operations are more important than SELECTs so optimisation
there would be appreciated (as major part of the work is writing
records)
� Good documentation
� Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie�s Weblog

http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source
license,
based on the standard BSD license and is, according to their website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database and
persistence engine in many Open Source Software projects (like open
office and JBoss) and even in commercial projects and products. (like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer
community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn�t adhere to the ACID properties fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the following
reasons:

1.Huge result sets are not likely to occur when a user is searching
his/her chat history. However, processing of huge result sets can be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn�t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (

http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are
most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail:
dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX: +33.1.77.62.47.31
http://sip-communicator.org PHONE: +33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

>
------------------------------------------------------------------------

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#12

Hi damencho

Well H2 is good,especially its support for transactions is
better....but it not really production ready...See
http://www.h2database.com/html/faq.html#reliable

On the other hand,HSQLDB is a mature product with proven reliability.

Thanks for your interest....It made things clearer for me

Regards

Ajay

···

On Tue, May 19, 2009 at 11:58 AM, Damian Minkov <damencho@sip-communicator.org> wrote:

Hi again,

first of all thank you for the detailed tests and reports. I thought that
HSQL will import the data when started which will cause slow startup and
high memory usage, but as I see this is not the case.
As you pointed maybe its due to some caching mechanism they got.
I also had a look at a link you pointed about performance comparison
between Embedded databases and in there you can see that HSQL is
significantly slower than H2 with some queries and uses twice more momory,
there they are talking about bad query optimizer but the test was with more
than 500K queries, which I think is also not our case.
So after your tests and comparing the sizes of the libs (h2 is twice the
size of hsql) and that openoffice are also using it - for me the choice is
hsql.

Cheers
damencho

AJAY CHHATWAL wrote:

Hi damencho,

This attachment is causing problems in being sent .... so i am sending
it separately

Regards,

Ajay

On Mon, May 18, 2009 at 11:09 PM, AJAY CHHATWAL >> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi damencho,

I have just completed testing as desired by you.
Although i do not claim that i performed performance benchmarking,I
also measured the memory usage as well as time taken for startup and
shutdown times and for table creation,insertion and selection for
current stable versions of HSQLDB,H2 and Derby for tables containing
the following no. of rows:
1
10
100
1000
10000
100000
500000
1000000

I have attached the results in the form of 6 text files generated by
my program with the name convention <DBNAME><s>.txt OR <DBNAME><c>.txt
Here,c denotes the results when the tables are first being created
while s denotes the results when records are being selected.

The <DBNAME>s.txt results are important as they measure the startup
times and shutdown after database creation.
(See DERBYs.txt,H2s.txt and HSQLs.txt)

Based on analysis of these files,I came to the following conclusions :

Conclusions:

1. Startup time is almost constant for HSQLDB and H2 while it
increases with table size for derby.

This is because in CACHED tables used for HSQLDB and H2,the data is
stored directly in the .data files and not as .script files. This is
indicated at one point in the documentation(
http://hsqldb.org/web/hsqlDocsFrame.html )
The data is stored as .script files for memory tables which are
anyways also limited by the total memory of the JVM and thus not
useful to our application.

About Derby's Behaviour, it is rather unexpected and I am not able to
understand the reason.

2.The shutdown (closing) time for HSQLDB increases with increase with
size of the table and is quite substantial compared to others.The
shutdown times for H2 also increases but very slowly and is quite less
compared to HSQLDB. The shutdown time for derby is negligible for
derby and almost constant.

This happens because HSQLDB backs the database at every proper shutdown.

3.The memory usage for derby is lower compared to H2 and HSQLDB . H2
and HSQLDB show similar usage till the Database becomes very
big(~500000 rows) at which H2 uses lesser memory

This is probably because of the caching behavior of the three DBMS

4.Derby is extreamely slow (by orders of magnitude) compared to H2 and
HSQLDB.
In fact it was so slow that after 1 run took 28 minutes,i didnt test
for larger tables
H2 and HSQLDB are somewhat comparable in performance but even if we
add the shutdown times,HSQLDB is quite faster than H2.Of course since
the queries tested here are trival,we cant really comment on both
these but it is clean that derby is way behind in terms of
performance.

5.The disk space occupied by the databases is the largest for H2 and
around half of that for HSQLDB and around 1.5 times of HSQLDB for
Derby.

I have attached the source code and the results with this mail.The
source contains a windows batch file for running the tests...It should
also run with minor changes on other platforms (*nix) as it only
contains runs javac and java

Regarding memory usage comparision,use can also see
http://www.h2database.com/html/performance.html
This is probably more accurate compared to my test

If you have any other queries please do contact me.

Regards

Ajay

On Mon, May 18, 2009 at 1:57 PM, Damian Minkov >>> <damencho@sip-communicator.org> wrote:

Hi Ajay,

I saw your report about HSQLDB and other features and performance
comparison.
But I'm wondering about something else. I have seen that actually hsqldb
keeps the data as something as sql script (text file with all create and
insert statements). So here are my thoughts :
- what is the startup time of the db engine, as it has to create all
tables
and insert all records before using them ?
- what is the memory usage on larger tables ?

And here is a pseudo program for testing this. In my opinion it will
answer
the questions above:

print memory ussage 1;
long firstTime = ....
init what is needed for the db
get the connection

print memory ussage 2;
print control time 1 (secondTime = currentTime - firstTime);
select from table A row with id = 1;

print memory ussage 3;
print control time 2 (currentTime - secondTime);

Run this program in two scenarios : 1. Have a table A with one record in
it
with id = 1;
2. Have a table A with one thousand records in it with id = 1;
Or more records if you want so we can have a better look how time
increases
when record number increases.

What do you think ?

Cheers
damencho

AJAY CHHATWAL wrote:

Sorry,forgot to include all the links

http://www.jepoirrier.net/blogimages/070917-db.html#jrat
http://www.jepoirrier.net/blog/2007/09/more-on-java-dbs-comparison/

On Sun, May 17, 2009 at 2:50 PM, AJAY CHHATWAL >>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,all

I have also found some more info on comparisons b/w H2,HSQLDB,Derby
and
Sqlite.
Plz see
http://www.jepoirrier.net/blog/2007/09/sqlitejdbc-worst-than-derby/

Regards,
Ajay

On Sun, May 17, 2009 at 2:08 PM, AJAY CHHATWAL >>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,

Here is a brief comparison of the features of the popular java
database
engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance
of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See
http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov >>>>>>> <emcho@sip-communicator.org> >>>>>>> wrote:

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how
it
compares to other popular projects such as Java DB/Derby, and the
Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >>>>>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing
chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat
history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the
History
records stored in the DB using various criteria, a relational
database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so
optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance
benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s
Weblog

http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our
application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and
server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source
license,
based on the standard BSD license and is, according to their
website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database
and
persistence engine in many Open Source Software projects (like
open
office and JBoss) and even in commercial projects and products.
(like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer
community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very
large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties
fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the
following
reasons:

1.Huge result sets are not likely to occur when a user is
searching
his/her chat history. However, processing of huge result sets can
be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (

http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well
suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are
most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail:
dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail:
dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX:
+33.1.77.62.47.31
http://sip-communicator.org PHONE:
+33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail:
dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail:
dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

>
------------------------------------------------------------------------

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#13

Hi Ajay, all,

Thank you for the great job you've done here Ajay!

Effectively, it seems that HSQL is a good choice. However the
transaction support may be a problem later when we will use the DBMS
for storing the MetaContactList. The wikipedia is saying that a full
transaction support is planned for next versions of HSQLDB. Anyway if
we really need a DBMS with full transaction support we could simply
implement another backend of the DB service for Derby or another DBMS,
less efficient but more reliable.

Ben

···

On Tue, May 19, 2009 at 08:38, AJAY CHHATWAL <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi damencho

Well H2 is good,especially its support for transactions is
better....but it not really production ready...See
http://www.h2database.com/html/faq.html#reliable

On the other hand,HSQLDB is a mature product with proven reliability.

Thanks for your interest....It made things clearer for me

Regards

Ajay

On Tue, May 19, 2009 at 11:58 AM, Damian Minkov > <damencho@sip-communicator.org> wrote:

Hi again,

first of all thank you for the detailed tests and reports. I thought that
HSQL will import the data when started which will cause slow startup and
high memory usage, but as I see this is not the case.
As you pointed maybe its due to some caching mechanism they got.
I also had a look at a link you pointed about performance comparison
between Embedded databases and in there you can see that HSQL is
significantly slower than H2 with some queries and uses twice more momory,
there they are talking about bad query optimizer but the test was with more
than 500K queries, which I think is also not our case.
So after your tests and comparing the sizes of the libs (h2 is twice the
size of hsql) and that openoffice are also using it - for me the choice is
hsql.

Cheers
damencho

AJAY CHHATWAL wrote:

Hi damencho,

This attachment is causing problems in being sent .... so i am sending
it separately

Regards,

Ajay

On Mon, May 18, 2009 at 11:09 PM, AJAY CHHATWAL >>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi damencho,

I have just completed testing as desired by you.
Although i do not claim that i performed performance benchmarking,I
also measured the memory usage as well as time taken for startup and
shutdown times and for table creation,insertion and selection for
current stable versions of HSQLDB,H2 and Derby for tables containing
the following no. of rows:
1
10
100
1000
10000
100000
500000
1000000

I have attached the results in the form of 6 text files generated by
my program with the name convention <DBNAME><s>.txt OR <DBNAME><c>.txt
Here,c denotes the results when the tables are first being created
while s denotes the results when records are being selected.

The <DBNAME>s.txt results are important as they measure the startup
times and shutdown after database creation.
(See DERBYs.txt,H2s.txt and HSQLs.txt)

Based on analysis of these files,I came to the following conclusions :

Conclusions:

1. Startup time is almost constant for HSQLDB and H2 while it
increases with table size for derby.

This is because in CACHED tables used for HSQLDB and H2,the data is
stored directly in the .data files and not as .script files. This is
indicated at one point in the documentation(
http://hsqldb.org/web/hsqlDocsFrame.html )
The data is stored as .script files for memory tables which are
anyways also limited by the total memory of the JVM and thus not
useful to our application.

About Derby's Behaviour, it is rather unexpected and I am not able to
understand the reason.

2.The shutdown (closing) time for HSQLDB increases with increase with
size of the table and is quite substantial compared to others.The
shutdown times for H2 also increases but very slowly and is quite less
compared to HSQLDB. The shutdown time for derby is negligible for
derby and almost constant.

This happens because HSQLDB backs the database at every proper shutdown.

3.The memory usage for derby is lower compared to H2 and HSQLDB . H2
and HSQLDB show similar usage till the Database becomes very
big(~500000 rows) at which H2 uses lesser memory

This is probably because of the caching behavior of the three DBMS

4.Derby is extreamely slow (by orders of magnitude) compared to H2 and
HSQLDB.
In fact it was so slow that after 1 run took 28 minutes,i didnt test
for larger tables
H2 and HSQLDB are somewhat comparable in performance but even if we
add the shutdown times,HSQLDB is quite faster than H2.Of course since
the queries tested here are trival,we cant really comment on both
these but it is clean that derby is way behind in terms of
performance.

5.The disk space occupied by the databases is the largest for H2 and
around half of that for HSQLDB and around 1.5 times of HSQLDB for
Derby.

I have attached the source code and the results with this mail.The
source contains a windows batch file for running the tests...It should
also run with minor changes on other platforms (*nix) as it only
contains runs javac and java

Regarding memory usage comparision,use can also see
http://www.h2database.com/html/performance.html
This is probably more accurate compared to my test

If you have any other queries please do contact me.

Regards

Ajay

On Mon, May 18, 2009 at 1:57 PM, Damian Minkov >>>> <damencho@sip-communicator.org> wrote:

Hi Ajay,

I saw your report about HSQLDB and other features and performance
comparison.
But I'm wondering about something else. I have seen that actually hsqldb
keeps the data as something as sql script (text file with all create and
insert statements). So here are my thoughts :
- what is the startup time of the db engine, as it has to create all
tables
and insert all records before using them ?
- what is the memory usage on larger tables ?

And here is a pseudo program for testing this. In my opinion it will
answer
the questions above:

print memory ussage 1;
long firstTime = ....
init what is needed for the db
get the connection

print memory ussage 2;
print control time 1 (secondTime = currentTime - firstTime);
select from table A row with id = 1;

print memory ussage 3;
print control time 2 (currentTime - secondTime);

Run this program in two scenarios : 1. Have a table A with one record in
it
with id = 1;
2. Have a table A with one thousand records in it with id = 1;
Or more records if you want so we can have a better look how time
increases
when record number increases.

What do you think ?

Cheers
damencho

AJAY CHHATWAL wrote:

Sorry,forgot to include all the links

http://www.jepoirrier.net/blogimages/070917-db.html#jrat
http://www.jepoirrier.net/blog/2007/09/more-on-java-dbs-comparison/

On Sun, May 17, 2009 at 2:50 PM, AJAY CHHATWAL >>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,all

I have also found some more info on comparisons b/w H2,HSQLDB,Derby
and
Sqlite.
Plz see
http://www.jepoirrier.net/blog/2007/09/sqlitejdbc-worst-than-derby/

Regards,
Ajay

On Sun, May 17, 2009 at 2:08 PM, AJAY CHHATWAL >>>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,

Here is a brief comparison of the features of the popular java
database
engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance
of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See
http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov >>>>>>>> <emcho@sip-communicator.org> >>>>>>>> wrote:

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how
it
compares to other popular projects such as Java DB/Derby, and the
Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >>>>>>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing
chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat
history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the
History
records stored in the DB using various criteria, a relational
database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so
optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance
benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s
Weblog

http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our
application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and
server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source
license,
based on the standard BSD license and is, according to their
website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database
and
persistence engine in many Open Source Software projects (like
open
office and JBoss) and even in commercial projects and products.
(like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer
community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very
large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties
fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the
following
reasons:

1.Huge result sets are not likely to occur when a user is
searching
his/her chat history. However, processing of huge result sets can
be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (

http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well
suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are
most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail:
dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail:
dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX:
+33.1.77.62.47.31
http://sip-communicator.org PHONE:
+33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail:
dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail:
dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

>
------------------------------------------------------------------------

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net


#14

Hi ben,all,

I have read a bit more about the transaction support in HSQLDB and I
believe that, for our purpose,HSQLDB provides adequate transaction
support since a READ_UNCOMMITED transaction isolation will not cause
any problems as we need to maintain only a single connection to the
database in the DB bundle.
See the relevant portion of its documentation at
http://hsqldb.org/doc/guide/ch02.html#N104FC

You may also find the following links helpful:

http://en.wikipedia.org/wiki/Isolation_(database_systems)
http://en.wikipedia.org/wiki/ACID

Regards

Ajay

···

On Tue, May 19, 2009 at 1:57 PM, Benoit Pradelle <b.pradelle@gmail.com> wrote:

Hi Ajay, all,

Thank you for the great job you've done here Ajay!

Effectively, it seems that HSQL is a good choice. However the
transaction support may be a problem later when we will use the DBMS
for storing the MetaContactList. The wikipedia is saying that a full
transaction support is planned for next versions of HSQLDB. Anyway if
we really need a DBMS with full transaction support we could simply
implement another backend of the DB service for Derby or another DBMS,
less efficient but more reliable.

Ben

On Tue, May 19, 2009 at 08:38, AJAY CHHATWAL > <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi damencho

Well H2 is good,especially its support for transactions is
better....but it not really production ready...See
http://www.h2database.com/html/faq.html#reliable

On the other hand,HSQLDB is a mature product with proven reliability.

Thanks for your interest....It made things clearer for me

Regards

Ajay

On Tue, May 19, 2009 at 11:58 AM, Damian Minkov >> <damencho@sip-communicator.org> wrote:

Hi again,

first of all thank you for the detailed tests and reports. I thought that
HSQL will import the data when started which will cause slow startup and
high memory usage, but as I see this is not the case.
As you pointed maybe its due to some caching mechanism they got.
I also had a look at a link you pointed about performance comparison
between Embedded databases and in there you can see that HSQL is
significantly slower than H2 with some queries and uses twice more momory,
there they are talking about bad query optimizer but the test was with more
than 500K queries, which I think is also not our case.
So after your tests and comparing the sizes of the libs (h2 is twice the
size of hsql) and that openoffice are also using it - for me the choice is
hsql.

Cheers
damencho

AJAY CHHATWAL wrote:

Hi damencho,

This attachment is causing problems in being sent .... so i am sending
it separately

Regards,

Ajay

On Mon, May 18, 2009 at 11:09 PM, AJAY CHHATWAL >>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi damencho,

I have just completed testing as desired by you.
Although i do not claim that i performed performance benchmarking,I
also measured the memory usage as well as time taken for startup and
shutdown times and for table creation,insertion and selection for
current stable versions of HSQLDB,H2 and Derby for tables containing
the following no. of rows:
1
10
100
1000
10000
100000
500000
1000000

I have attached the results in the form of 6 text files generated by
my program with the name convention <DBNAME><s>.txt OR <DBNAME><c>.txt
Here,c denotes the results when the tables are first being created
while s denotes the results when records are being selected.

The <DBNAME>s.txt results are important as they measure the startup
times and shutdown after database creation.
(See DERBYs.txt,H2s.txt and HSQLs.txt)

Based on analysis of these files,I came to the following conclusions :

Conclusions:

1. Startup time is almost constant for HSQLDB and H2 while it
increases with table size for derby.

This is because in CACHED tables used for HSQLDB and H2,the data is
stored directly in the .data files and not as .script files. This is
indicated at one point in the documentation(
http://hsqldb.org/web/hsqlDocsFrame.html )
The data is stored as .script files for memory tables which are
anyways also limited by the total memory of the JVM and thus not
useful to our application.

About Derby's Behaviour, it is rather unexpected and I am not able to
understand the reason.

2.The shutdown (closing) time for HSQLDB increases with increase with
size of the table and is quite substantial compared to others.The
shutdown times for H2 also increases but very slowly and is quite less
compared to HSQLDB. The shutdown time for derby is negligible for
derby and almost constant.

This happens because HSQLDB backs the database at every proper shutdown.

3.The memory usage for derby is lower compared to H2 and HSQLDB . H2
and HSQLDB show similar usage till the Database becomes very
big(~500000 rows) at which H2 uses lesser memory

This is probably because of the caching behavior of the three DBMS

4.Derby is extreamely slow (by orders of magnitude) compared to H2 and
HSQLDB.
In fact it was so slow that after 1 run took 28 minutes,i didnt test
for larger tables
H2 and HSQLDB are somewhat comparable in performance but even if we
add the shutdown times,HSQLDB is quite faster than H2.Of course since
the queries tested here are trival,we cant really comment on both
these but it is clean that derby is way behind in terms of
performance.

5.The disk space occupied by the databases is the largest for H2 and
around half of that for HSQLDB and around 1.5 times of HSQLDB for
Derby.

I have attached the source code and the results with this mail.The
source contains a windows batch file for running the tests...It should
also run with minor changes on other platforms (*nix) as it only
contains runs javac and java

Regarding memory usage comparision,use can also see
http://www.h2database.com/html/performance.html
This is probably more accurate compared to my test

If you have any other queries please do contact me.

Regards

Ajay

On Mon, May 18, 2009 at 1:57 PM, Damian Minkov >>>>> <damencho@sip-communicator.org> wrote:

Hi Ajay,

I saw your report about HSQLDB and other features and performance
comparison.
But I'm wondering about something else. I have seen that actually hsqldb
keeps the data as something as sql script (text file with all create and
insert statements). So here are my thoughts :
- what is the startup time of the db engine, as it has to create all
tables
and insert all records before using them ?
- what is the memory usage on larger tables ?

And here is a pseudo program for testing this. In my opinion it will
answer
the questions above:

print memory ussage 1;
long firstTime = ....
init what is needed for the db
get the connection

print memory ussage 2;
print control time 1 (secondTime = currentTime - firstTime);
select from table A row with id = 1;

print memory ussage 3;
print control time 2 (currentTime - secondTime);

Run this program in two scenarios : 1. Have a table A with one record in
it
with id = 1;
2. Have a table A with one thousand records in it with id = 1;
Or more records if you want so we can have a better look how time
increases
when record number increases.

What do you think ?

Cheers
damencho

AJAY CHHATWAL wrote:

Sorry,forgot to include all the links

http://www.jepoirrier.net/blogimages/070917-db.html#jrat
http://www.jepoirrier.net/blog/2007/09/more-on-java-dbs-comparison/

On Sun, May 17, 2009 at 2:50 PM, AJAY CHHATWAL >>>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,all

I have also found some more info on comparisons b/w H2,HSQLDB,Derby
and
Sqlite.
Plz see
http://www.jepoirrier.net/blog/2007/09/sqlitejdbc-worst-than-derby/

Regards,
Ajay

On Sun, May 17, 2009 at 2:08 PM, AJAY CHHATWAL >>>>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi Emil,

Here is a brief comparison of the features of the popular java
database
engines.

http://www.h2database.com/html/features.html#comparison

The benchmarks reffered to in my first mail compare the performance
of
the DB engines.
Also see:http://www.h2database.com/html/performance.html

Basically, Derby is good feature wise and support wise....But its
performance is very low...

Also,Sqllite is not a pure java DB and so its use with JDBC incurs a
significant performance penalty.
See
http://www.nabble.com/SQLite-JDBC-driver-performance-td21561118.html

I believe H2 and HSQLDB are the only ones we should seriously
consider....and maybe derby if we really require extensive
support...as sun provides support for Java DB

Please contact me if you need further details

Regards

Ajay

On Sun, May 17, 2009 at 5:56 AM, Emil Ivov >>>>>>>>> <emcho@sip-communicator.org> >>>>>>>>> wrote:

Hey Ajay,

Thanks for the extensive presentation of the HSQLDB project.

I was curious however if you could give us a few more details on how
it
compares to other popular projects such as Java DB/Derby, and the
Java
version of sqlite?

Cheers
Emil

AJAY CHHATWAL wrote:

It seems that new version has fixed both the problems with HSQLDB .
http://hsqldb.org/web/features190.html

Regards,

Ajay

On Thu, May 14, 2009 at 12:18 AM, AJAY CHHATWAL >>>>>>>>>>> <ajay.chhatwal.cse07@itbhu.ac.in> wrote:

Hi all,

I am Ajay Chhatwal and am working on my GSoC project – Storing
chat
history in a database.

The main objective of my project is to select and use a suitable
embedded database engine available for Java to store the chat
history
for SIP Communicator.

This task requires a database which can perform simple SELECT and
INSERT queries efficiently. Also, since we need to search the
History
records stored in the DB using various criteria, a relational
database
would be more suited the application than an object based DB .

Selection criteria:

• Should be free and open source with compatible license
• Should be reliable
• Should support JDBC and act as a relational database
• INSERT operations are more important than SELECTs so
optimisation
there would be appreciated (as major part of the work is writing
records)
• Good documentation
• Recently updated and under active development

Evaluation:

I have based my evaluation on the following performance
benchmarks:

1. PolePosition (an open source database benchmark)
http://www.polepos.org

2. Performance Benchmarking of Embedded Databases on Jamie’s
Weblog

http://jamie.ideasasylum.com/2005/03/performance-benchmarking-of-embedded-databases/

From both these it is clear that for the purpose of our
application,
HSQLDB has the best performance.

So, I first investigate the suitability of HSQLDB.

HSQLDB (HyperSQL DataBase) is the leading SQL relational database
engine written in Java. It has a JDBC driver and supports a rich
subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003
enhancements. It offers a small, fast database engine which offers
both in-memory and disk-based tables and supports embedded and
server
modes.

Pros:

1. Extremely good performance (see the benchmark results above)

2. Completely free to use and distribute under an open source
license,
based on the standard BSD license and is, according to their
website,
fully compatible with all major open source licenses

3. Stable and reliable and is currently being used as a database
and
persistence engine in many Open Source Software projects (like
open
office and JBoss) and even in commercial projects and products.
(like
Mathematica )
( http://hsqldb.org/web/hsqlUsing.html )

4. Very active and popular project with a vibrant developer
community.

5. Extensive documentation is included.

Cons:

1.All the rows in the result set are built in memory, so very
large
result sets may not be possible.
(http://hsqldb.sourceforge.net/doc/guide/ch05.html )

2.It seems that HSQLDB doesn’t adhere to the ACID properties
fully.
(http://en.wikipedia.org/wiki/ACID )

I believe that the Pros out weight the cons because of the
following
reasons:

1.Huge result sets are not likely to occur when a user is
searching
his/her chat history. However, processing of huge result sets can
be
done using a simple workaround using ID fields and select multiple
'smaller' blocks.
(http://hsqldb.org/web/hsqlFAQ.html)

2.The DB is reliable enough for storage of chat history which, I
believe, doesn’t really require being ACID safe as each query is
expected to be independent of the other.
Also, there are ways to achieve this if required (

http://www.mail-archive.com/hsqldb-developers@lists.sourceforge.net/msg00692.html
).
Also, version 1.9(alpha-2) supports READ COMMITED mode also.
(http://hsqldb.org/web/features190.html)

On the basis of these observations, I feel that HSQLDB is well
suited
to the purpose of storing chat history.

These are my initial observations and I wish to have some feedback
from the community.

Suggestions, comments as well as criticism from the community are
most
appreciated.

Regards

Ajay

---------------------------------------------------------------------
To unsubscribe, e-mail:
dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail:
dev-help@sip-communicator.dev.java.net

--
Emil Ivov, Ph.D. 30a rue de la Patrie
Project Lead 67300 Schiltigheim
SIP Communicator
emcho@sip-communicator.org FAX:
+33.1.77.62.47.31
http://sip-communicator.org PHONE:
+33.1.77.62.43.30

---------------------------------------------------------------------
To unsubscribe, e-mail:
dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail:
dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

>
------------------------------------------------------------------------

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@sip-communicator.dev.java.net
For additional commands, e-mail: dev-help@sip-communicator.dev.java.net