HSQLDB › HSQLDB - User

Fwd: HSQL/MySQL query performance

Classic

List

Threaded

9 messages Options

Viktor Ádám

Fwd: HSQL/MySQL query performance

Hi!

We are migrating our application from MySQL to HSQLDB to have greater
control over the database. Everything works great except that queries
are slower than they were on MySQL.
I've attached a simple test running the same CREATE/INSERT/SELECT
statements on HSQL and MySQL and the produced output also. In the
attached output MySQL queries are 4-5x times faster and on another
development machine this ratio is about 8-10x.
Can you give as a hint about what we're doing wrong? Are there any
configuration parameters which could be tweaked?

Our test PC was:
CPU: Intel Core i5-2400 @ 3.30GHz
RAM: 4GB
OS: Windows 7 Home Premium
MySQL: 5.5.11

Thank you in advance!

Best regards,
Viktor Ádám

------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

dbperformance.txt (2K) Download Attachment

DBPerformanceTester.java (7K) Download Attachment

Fred Toussi-2

Re: Fwd: HSQL/MySQL query performance

The log shows the inserts are 3.5 times faster with HSQLDB.

As for your SELECT statements, you are selecting all the rows in the
table. The default CACHE ROWS and CACHE SIZE settings for HSQLDB allow
fewer rows, so each time you select, lots of rows are fetched from the
disk.

Just increase the above settings until SELECT speed is what you want. I
think it should be faster than MySQL.

Fred

On Wed, Dec 18, 2013, at 10:22, Viktor Ádám wrote:

> Hi!
>
> We are migrating our application from MySQL to HSQLDB to have greater
> control over the database. Everything works great except that queries
> are slower than they were on MySQL.
> I've attached a simple test running the same CREATE/INSERT/SELECT
> statements on HSQL and MySQL and the produced output also. In the
> attached output MySQL queries are 4-5x times faster and on another
> development machine this ratio is about 8-10x.
> Can you give as a hint about what we're doing wrong? Are there any
> configuration parameters which could be tweaked?
>
> Our test PC was:
> CPU: Intel Core i5-2400 @ 3.30GHz
> RAM: 4GB
> OS: Windows 7 Home Premium
> MySQL: 5.5.11
>
> Thank you in advance!
>
> Best regards,
> Viktor Ádám
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into
> your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> Email had 2 attachments:
> + dbperformance.txt
> 3k (text/plain)
> + DBPerformanceTester.java
> 8k (text/x-java)

------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Viktor Ádám

Re: Fwd: HSQL/MySQL query performance

Hi Fred,

thanks for your help! It is true that inserts are much faster and that
is very impressive.
I wasn't entirely informative about our setup. We would like to use
HSQLDB in server mode. I've attached a new test and its result where
we executed the sample with a server HSQLDB instance, an in-process
instance (file:) and with MySQL. The parameters you suggested
definitely helps in in-process mode (our select times has gone down to
about 10 ms from about 150-200 ms). In server mode there is a slight
improvement but it is still slower for us than MySQL.
Do you think that it should be faster even in server mode with HSQL protocol?

Thanks again for your help!

Viktor

On Thu, Dec 19, 2013 at 1:08 AM, Fred Toussi
<[hidden email]> wrote:

> The log shows the inserts are 3.5 times faster with HSQLDB.
>
> As for your SELECT statements, you are selecting all the rows in the
> table. The default CACHE ROWS and CACHE SIZE settings for HSQLDB allow
> fewer rows, so each time you select, lots of rows are fetched from the
> disk.
>
> Just increase the above settings until SELECT speed is what you want. I
> think it should be faster than MySQL.
>
> Fred
>
> On Wed, Dec 18, 2013, at 10:22, Viktor Ádám wrote:
>> Hi!
>>
>> We are migrating our application from MySQL to HSQLDB to have greater
>> control over the database. Everything works great except that queries
>> are slower than they were on MySQL.
>> I've attached a simple test running the same CREATE/INSERT/SELECT
>> statements on HSQL and MySQL and the produced output also. In the
>> attached output MySQL queries are 4-5x times faster and on another
>> development machine this ratio is about 8-10x.
>> Can you give as a hint about what we're doing wrong? Are there any
>> configuration parameters which could be tweaked?
>>
>> Our test PC was:
>> CPU: Intel Core i5-2400 @ 3.30GHz
>> RAM: 4GB
>> OS: Windows 7 Home Premium
>> MySQL: 5.5.11
>>
>> Thank you in advance!
>>
>> Best regards,
>> Viktor Ádám
>> ------------------------------------------------------------------------------
>> Rapidly troubleshoot problems before they affect your business. Most IT
>> organizations don't have a clear picture of how application performance
>> affects their revenue. With AppDynamics, you get 100% visibility into
>> your
>> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> Pro!
>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Hsqldb-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> Email had 2 attachments:
>> + dbperformance.txt
>> 3k (text/plain)
>> + DBPerformanceTester.java
>> 8k (text/x-java)
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

dbperformance2.txt (5K) Download Attachment

DBPerformanceTester.java (8K) Download Attachment

Fred Toussi-2

Re: Fwd: HSQL/MySQL query performance

You are not setting the properties for the database on the server
correctly. The lines starting with the ones below have no effect.

HsqlProperties props = new HsqlProperties();
props.setProperty(HsqlDatabaseProperties.hsqldb_tx,
"MVCC");

The properties for a server can contain only server properties. These
properties are like server.silent=false, etc.
The database properties for a server are set on the URL of the database
in this call: server.setDatabasePath(0, "file:db/testdb2"); The
properties should be lowercase.

In addition, you should never access HSQLDB's classes that are not
documented for external use. These include org.hsqldb.DatabaseManager
etc.

Fred

On Thu, Dec 19, 2013, at 8:38, Viktor Ádám wrote:

> Hi Fred,
>
> thanks for your help! It is true that inserts are much faster and that
> is very impressive.
> I wasn't entirely informative about our setup. We would like to use
> HSQLDB in server mode. I've attached a new test and its result where
> we executed the sample with a server HSQLDB instance, an in-process
> instance (file:) and with MySQL. The parameters you suggested
> definitely helps in in-process mode (our select times has gone down to
> about 10 ms from about 150-200 ms). In server mode there is a slight
> improvement but it is still slower for us than MySQL.
> Do you think that it should be faster even in server mode with HSQL
> protocol?
>
> Thanks again for your help!
>
> Viktor
>
> On Thu, Dec 19, 2013 at 1:08 AM, Fred Toussi
> <[hidden email]> wrote:
> > The log shows the inserts are 3.5 times faster with HSQLDB.
> >
> > As for your SELECT statements, you are selecting all the rows in the
> > table. The default CACHE ROWS and CACHE SIZE settings for HSQLDB allow
> > fewer rows, so each time you select, lots of rows are fetched from the
> > disk.
> >
> > Just increase the above settings until SELECT speed is what you want. I
> > think it should be faster than MySQL.
> >
> > Fred
> >
> > On Wed, Dec 18, 2013, at 10:22, Viktor Ádám wrote:
> >> Hi!
> >>
> >> We are migrating our application from MySQL to HSQLDB to have greater
> >> control over the database. Everything works great except that queries
> >> are slower than they were on MySQL.
> >> I've attached a simple test running the same CREATE/INSERT/SELECT
> >> statements on HSQL and MySQL and the produced output also. In the
> >> attached output MySQL queries are 4-5x times faster and on another
> >> development machine this ratio is about 8-10x.
> >> Can you give as a hint about what we're doing wrong? Are there any
> >> configuration parameters which could be tweaked?
> >>
> >> Our test PC was:
> >> CPU: Intel Core i5-2400 @ 3.30GHz
> >> RAM: 4GB
> >> OS: Windows 7 Home Premium
> >> MySQL: 5.5.11
> >>
> >> Thank you in advance!
> >>
> >> Best regards,
> >> Viktor Ádám
> >> ------------------------------------------------------------------------------
> >> Rapidly troubleshoot problems before they affect your business. Most IT
> >> organizations don't have a clear picture of how application performance
> >> affects their revenue. With AppDynamics, you get 100% visibility into
> >> your
> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> >> Pro!
> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> _______________________________________________
> >> Hsqldb-user mailing list
> >> [hidden email]
> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> Email had 2 attachments:
> >> + dbperformance.txt
> >> 3k (text/plain)
> >> + DBPerformanceTester.java
> >> 8k (text/x-java)
> >
> > ------------------------------------------------------------------------------
> > Rapidly troubleshoot problems before they affect your business. Most IT
> > organizations don't have a clear picture of how application performance
> > affects their revenue. With AppDynamics, you get 100% visibility into your
> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> > _______________________________________________
> > Hsqldb-user mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into
> your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> Email had 2 attachments:
> + dbperformance2.txt
> 5k (text/plain)
> + DBPerformanceTester.java
> 9k (text/x-java)

Viktor Ádám

Re: Fwd: HSQL/MySQL query performance

Thanks for clearing that up!
However changing the code does not help on HSQL server mode speed for us.

StringBuilder pathBuilder = new StringBuilder("file:/home/swn/db/testdb2");
pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx).append("=").append("MVCC");
pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_level).append("=").append("READ_COMMITTED");
pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_conflict_rollback).append("=").append(true);
pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_default_table_type).append("=").append("CACHED");
pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_rows).append("=").append(100000);
pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_size).append("=").append(100000);
pathBuilder.append(";").append("user").append("=").append(user);
pathBuilder.append(";").append("password").append("=").append(password);
server.setDatabasePath(0, pathBuilder.toString());

Viktor

On Thu, Dec 19, 2013 at 10:15 AM, Fred Toussi
<[hidden email]> wrote:

> You are not setting the properties for the database on the server
> correctly. The lines starting with the ones below have no effect.
>
> HsqlProperties props = new HsqlProperties();
> props.setProperty(HsqlDatabaseProperties.hsqldb_tx,
> "MVCC");
>
> The properties for a server can contain only server properties. These
> properties are like server.silent=false, etc.
> The database properties for a server are set on the URL of the database
> in this call: server.setDatabasePath(0, "file:db/testdb2"); The
> properties should be lowercase.
>
> In addition, you should never access HSQLDB's classes that are not
> documented for external use. These include org.hsqldb.DatabaseManager
> etc.
>
> Fred
>
> On Thu, Dec 19, 2013, at 8:38, Viktor Ádám wrote:
>> Hi Fred,
>>
>> thanks for your help! It is true that inserts are much faster and that
>> is very impressive.
>> I wasn't entirely informative about our setup. We would like to use
>> HSQLDB in server mode. I've attached a new test and its result where
>> we executed the sample with a server HSQLDB instance, an in-process
>> instance (file:) and with MySQL. The parameters you suggested
>> definitely helps in in-process mode (our select times has gone down to
>> about 10 ms from about 150-200 ms). In server mode there is a slight
>> improvement but it is still slower for us than MySQL.
>> Do you think that it should be faster even in server mode with HSQL
>> protocol?
>>
>> Thanks again for your help!
>>
>> Viktor
>>
>> On Thu, Dec 19, 2013 at 1:08 AM, Fred Toussi
>> <[hidden email]> wrote:
>> > The log shows the inserts are 3.5 times faster with HSQLDB.
>> >
>> > As for your SELECT statements, you are selecting all the rows in the
>> > table. The default CACHE ROWS and CACHE SIZE settings for HSQLDB allow
>> > fewer rows, so each time you select, lots of rows are fetched from the
>> > disk.
>> >
>> > Just increase the above settings until SELECT speed is what you want. I
>> > think it should be faster than MySQL.
>> >
>> > Fred
>> >
>> > On Wed, Dec 18, 2013, at 10:22, Viktor Ádám wrote:
>> >> Hi!
>> >>
>> >> We are migrating our application from MySQL to HSQLDB to have greater
>> >> control over the database. Everything works great except that queries
>> >> are slower than they were on MySQL.
>> >> I've attached a simple test running the same CREATE/INSERT/SELECT
>> >> statements on HSQL and MySQL and the produced output also. In the
>> >> attached output MySQL queries are 4-5x times faster and on another
>> >> development machine this ratio is about 8-10x.
>> >> Can you give as a hint about what we're doing wrong? Are there any
>> >> configuration parameters which could be tweaked?
>> >>
>> >> Our test PC was:
>> >> CPU: Intel Core i5-2400 @ 3.30GHz
>> >> RAM: 4GB
>> >> OS: Windows 7 Home Premium
>> >> MySQL: 5.5.11
>> >>
>> >> Thank you in advance!
>> >>
>> >> Best regards,
>> >> Viktor Ádám
>> >> ------------------------------------------------------------------------------
>> >> Rapidly troubleshoot problems before they affect your business. Most IT
>> >> organizations don't have a clear picture of how application performance
>> >> affects their revenue. With AppDynamics, you get 100% visibility into
>> >> your
>> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> >> Pro!
>> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> >> _______________________________________________
>> >> Hsqldb-user mailing list
>> >> [hidden email]
>> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> >> Email had 2 attachments:
>> >> + dbperformance.txt
>> >> 3k (text/plain)
>> >> + DBPerformanceTester.java
>> >> 8k (text/x-java)
>> >
>> > ------------------------------------------------------------------------------
>> > Rapidly troubleshoot problems before they affect your business. Most IT
>> > organizations don't have a clear picture of how application performance
>> > affects their revenue. With AppDynamics, you get 100% visibility into your
>> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
>> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> > _______________________________________________
>> > Hsqldb-user mailing list
>> > [hidden email]
>> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> ------------------------------------------------------------------------------
>> Rapidly troubleshoot problems before they affect your business. Most IT
>> organizations don't have a clear picture of how application performance
>> affects their revenue. With AppDynamics, you get 100% visibility into
>> your
>> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> Pro!
>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Hsqldb-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> Email had 2 attachments:
>> + dbperformance2.txt
>> 5k (text/plain)
>> + DBPerformanceTester.java
>> 9k (text/x-java)
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Fred Toussi-2

Re: Fwd: HSQL/MySQL query performance

Try again. The settings are not being applied if you don't see any speed
difference.

The easy way to do this is create the database with an in-process
connection that applies the properties then performs SHUTDOWN without
populating it. You then start the server without setting the database
properties and perform the tests. As stated before, use lowrecase for
peroperties.

Fred

On Thu, Dec 19, 2013, at 9:48, Viktor Ádám wrote:

> Thanks for clearing that up!
> However changing the code does not help on HSQL server mode speed for us.
>
> StringBuilder pathBuilder = new
> StringBuilder("file:/home/swn/db/testdb2");
> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx).append("=").append("MVCC");
> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_level).append("=").append("READ_COMMITTED");
> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_conflict_rollback).append("=").append(true);
> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_default_table_type).append("=").append("CACHED");
> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_rows).append("=").append(100000);
> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_size).append("=").append(100000);
> pathBuilder.append(";").append("user").append("=").append(user);
> pathBuilder.append(";").append("password").append("=").append(password);
> server.setDatabasePath(0, pathBuilder.toString());
>
> Viktor
>
> On Thu, Dec 19, 2013 at 10:15 AM, Fred Toussi
> <[hidden email]> wrote:
> > You are not setting the properties for the database on the server
> > correctly. The lines starting with the ones below have no effect.
> >
> > HsqlProperties props = new HsqlProperties();
> > props.setProperty(HsqlDatabaseProperties.hsqldb_tx,
> > "MVCC");
> >
> > The properties for a server can contain only server properties. These
> > properties are like server.silent=false, etc.
> > The database properties for a server are set on the URL of the database
> > in this call: server.setDatabasePath(0, "file:db/testdb2"); The
> > properties should be lowercase.
> >
> > In addition, you should never access HSQLDB's classes that are not
> > documented for external use. These include org.hsqldb.DatabaseManager
> > etc.
> >
> > Fred
> >
> > On Thu, Dec 19, 2013, at 8:38, Viktor Ádám wrote:
> >> Hi Fred,
> >>
> >> thanks for your help! It is true that inserts are much faster and that
> >> is very impressive.
> >> I wasn't entirely informative about our setup. We would like to use
> >> HSQLDB in server mode. I've attached a new test and its result where
> >> we executed the sample with a server HSQLDB instance, an in-process
> >> instance (file:) and with MySQL. The parameters you suggested
> >> definitely helps in in-process mode (our select times has gone down to
> >> about 10 ms from about 150-200 ms). In server mode there is a slight
> >> improvement but it is still slower for us than MySQL.
> >> Do you think that it should be faster even in server mode with HSQL
> >> protocol?
> >>
> >> Thanks again for your help!
> >>
> >> Viktor
> >>
> >> On Thu, Dec 19, 2013 at 1:08 AM, Fred Toussi
> >> <[hidden email]> wrote:
> >> > The log shows the inserts are 3.5 times faster with HSQLDB.
> >> >
> >> > As for your SELECT statements, you are selecting all the rows in the
> >> > table. The default CACHE ROWS and CACHE SIZE settings for HSQLDB allow
> >> > fewer rows, so each time you select, lots of rows are fetched from the
> >> > disk.
> >> >
> >> > Just increase the above settings until SELECT speed is what you want. I
> >> > think it should be faster than MySQL.
> >> >
> >> > Fred
> >> >
> >> > On Wed, Dec 18, 2013, at 10:22, Viktor Ádám wrote:
> >> >> Hi!
> >> >>
> >> >> We are migrating our application from MySQL to HSQLDB to have greater
> >> >> control over the database. Everything works great except that queries
> >> >> are slower than they were on MySQL.
> >> >> I've attached a simple test running the same CREATE/INSERT/SELECT
> >> >> statements on HSQL and MySQL and the produced output also. In the
> >> >> attached output MySQL queries are 4-5x times faster and on another
> >> >> development machine this ratio is about 8-10x.
> >> >> Can you give as a hint about what we're doing wrong? Are there any
> >> >> configuration parameters which could be tweaked?
> >> >>
> >> >> Our test PC was:
> >> >> CPU: Intel Core i5-2400 @ 3.30GHz
> >> >> RAM: 4GB
> >> >> OS: Windows 7 Home Premium
> >> >> MySQL: 5.5.11
> >> >>
> >> >> Thank you in advance!
> >> >>
> >> >> Best regards,
> >> >> Viktor Ádám
> >> >> ------------------------------------------------------------------------------
> >> >> Rapidly troubleshoot problems before they affect your business. Most IT
> >> >> organizations don't have a clear picture of how application performance
> >> >> affects their revenue. With AppDynamics, you get 100% visibility into
> >> >> your
> >> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> >> >> Pro!
> >> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> >> _______________________________________________
> >> >> Hsqldb-user mailing list
> >> >> [hidden email]
> >> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >> Email had 2 attachments:
> >> >> + dbperformance.txt
> >> >> 3k (text/plain)
> >> >> + DBPerformanceTester.java
> >> >> 8k (text/x-java)
> >> >
> >> > ------------------------------------------------------------------------------
> >> > Rapidly troubleshoot problems before they affect your business. Most IT
> >> > organizations don't have a clear picture of how application performance
> >> > affects their revenue. With AppDynamics, you get 100% visibility into your
> >> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> >> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> > _______________________________________________
> >> > Hsqldb-user mailing list
> >> > [hidden email]
> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> ------------------------------------------------------------------------------
> >> Rapidly troubleshoot problems before they affect your business. Most IT
> >> organizations don't have a clear picture of how application performance
> >> affects their revenue. With AppDynamics, you get 100% visibility into
> >> your
> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> >> Pro!
> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> _______________________________________________
> >> Hsqldb-user mailing list
> >> [hidden email]
> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> Email had 2 attachments:
> >> + dbperformance2.txt
> >> 5k (text/plain)
> >> + DBPerformanceTester.java
> >> 9k (text/x-java)
> >
> > ------------------------------------------------------------------------------
> > Rapidly troubleshoot problems before they affect your business. Most IT
> > organizations don't have a clear picture of how application performance
> > affects their revenue. With AppDynamics, you get 100% visibility into your
> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> > _______________________________________________
> > Hsqldb-user mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into
> your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Viktor Ádám

Re: Fwd: HSQL/MySQL query performance

I see. There is definitely an improvement:
Select.Walk #1: 1290 -> 669
Select.Walk #2: 762 -> 503
Select.Walk #3: 805 -> 487
Select.Walk #4: 743 -> 494
Select.Walk #5: 776 -> 493
Select.Walk #6: 776 -> 486
Select.Walk #7: 760 -> 488
Select.Walk #8: 758 -> 486
Select.Walk #9: 775 -> 487
Select.Walk #10: 756 -> 488

Do you think this is about as good as it gets? (To compare, MySQL
produces about 150 ms execution times on these queries on this
machine).

Thanks,
Viktor

On Thu, Dec 19, 2013 at 11:02 AM, Fred Toussi
<[hidden email]> wrote:

> Try again. The settings are not being applied if you don't see any speed
> difference.
>
> The easy way to do this is create the database with an in-process
> connection that applies the properties then performs SHUTDOWN without
> populating it. You then start the server without setting the database
> properties and perform the tests. As stated before, use lowrecase for
> peroperties.
>
> Fred
>
> On Thu, Dec 19, 2013, at 9:48, Viktor Ádám wrote:
>> Thanks for clearing that up!
>> However changing the code does not help on HSQL server mode speed for us.
>>
>> StringBuilder pathBuilder = new
>> StringBuilder("file:/home/swn/db/testdb2");
>> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx).append("=").append("MVCC");
>> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_level).append("=").append("READ_COMMITTED");
>> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_conflict_rollback).append("=").append(true);
>> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_default_table_type).append("=").append("CACHED");
>> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_rows).append("=").append(100000);
>> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_size).append("=").append(100000);
>> pathBuilder.append(";").append("user").append("=").append(user);
>> pathBuilder.append(";").append("password").append("=").append(password);
>> server.setDatabasePath(0, pathBuilder.toString());
>>
>> Viktor
>>
>> On Thu, Dec 19, 2013 at 10:15 AM, Fred Toussi
>> <[hidden email]> wrote:
>> > You are not setting the properties for the database on the server
>> > correctly. The lines starting with the ones below have no effect.
>> >
>> > HsqlProperties props = new HsqlProperties();
>> > props.setProperty(HsqlDatabaseProperties.hsqldb_tx,
>> > "MVCC");
>> >
>> > The properties for a server can contain only server properties. These
>> > properties are like server.silent=false, etc.
>> > The database properties for a server are set on the URL of the database
>> > in this call: server.setDatabasePath(0, "file:db/testdb2"); The
>> > properties should be lowercase.
>> >
>> > In addition, you should never access HSQLDB's classes that are not
>> > documented for external use. These include org.hsqldb.DatabaseManager
>> > etc.
>> >
>> > Fred
>> >
>> > On Thu, Dec 19, 2013, at 8:38, Viktor Ádám wrote:
>> >> Hi Fred,
>> >>
>> >> thanks for your help! It is true that inserts are much faster and that
>> >> is very impressive.
>> >> I wasn't entirely informative about our setup. We would like to use
>> >> HSQLDB in server mode. I've attached a new test and its result where
>> >> we executed the sample with a server HSQLDB instance, an in-process
>> >> instance (file:) and with MySQL. The parameters you suggested
>> >> definitely helps in in-process mode (our select times has gone down to
>> >> about 10 ms from about 150-200 ms). In server mode there is a slight
>> >> improvement but it is still slower for us than MySQL.
>> >> Do you think that it should be faster even in server mode with HSQL
>> >> protocol?
>> >>
>> >> Thanks again for your help!
>> >>
>> >> Viktor
>> >>
>> >> On Thu, Dec 19, 2013 at 1:08 AM, Fred Toussi
>> >> <[hidden email]> wrote:
>> >> > The log shows the inserts are 3.5 times faster with HSQLDB.
>> >> >
>> >> > As for your SELECT statements, you are selecting all the rows in the
>> >> > table. The default CACHE ROWS and CACHE SIZE settings for HSQLDB allow
>> >> > fewer rows, so each time you select, lots of rows are fetched from the
>> >> > disk.
>> >> >
>> >> > Just increase the above settings until SELECT speed is what you want. I
>> >> > think it should be faster than MySQL.
>> >> >
>> >> > Fred
>> >> >
>> >> > On Wed, Dec 18, 2013, at 10:22, Viktor Ádám wrote:
>> >> >> Hi!
>> >> >>
>> >> >> We are migrating our application from MySQL to HSQLDB to have greater
>> >> >> control over the database. Everything works great except that queries
>> >> >> are slower than they were on MySQL.
>> >> >> I've attached a simple test running the same CREATE/INSERT/SELECT
>> >> >> statements on HSQL and MySQL and the produced output also. In the
>> >> >> attached output MySQL queries are 4-5x times faster and on another
>> >> >> development machine this ratio is about 8-10x.
>> >> >> Can you give as a hint about what we're doing wrong? Are there any
>> >> >> configuration parameters which could be tweaked?
>> >> >>
>> >> >> Our test PC was:
>> >> >> CPU: Intel Core i5-2400 @ 3.30GHz
>> >> >> RAM: 4GB
>> >> >> OS: Windows 7 Home Premium
>> >> >> MySQL: 5.5.11
>> >> >>
>> >> >> Thank you in advance!
>> >> >>
>> >> >> Best regards,
>> >> >> Viktor Ádám
>> >> >> ------------------------------------------------------------------------------
>> >> >> Rapidly troubleshoot problems before they affect your business. Most IT
>> >> >> organizations don't have a clear picture of how application performance
>> >> >> affects their revenue. With AppDynamics, you get 100% visibility into
>> >> >> your
>> >> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> >> >> Pro!
>> >> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> >> >> _______________________________________________
>> >> >> Hsqldb-user mailing list
>> >> >> [hidden email]
>> >> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> >> >> Email had 2 attachments:
>> >> >> + dbperformance.txt
>> >> >> 3k (text/plain)
>> >> >> + DBPerformanceTester.java
>> >> >> 8k (text/x-java)
>> >> >
>> >> > ------------------------------------------------------------------------------
>> >> > Rapidly troubleshoot problems before they affect your business. Most IT
>> >> > organizations don't have a clear picture of how application performance
>> >> > affects their revenue. With AppDynamics, you get 100% visibility into your
>> >> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
>> >> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> >> > _______________________________________________
>> >> > Hsqldb-user mailing list
>> >> > [hidden email]
>> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> >> ------------------------------------------------------------------------------
>> >> Rapidly troubleshoot problems before they affect your business. Most IT
>> >> organizations don't have a clear picture of how application performance
>> >> affects their revenue. With AppDynamics, you get 100% visibility into
>> >> your
>> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> >> Pro!
>> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> >> _______________________________________________
>> >> Hsqldb-user mailing list
>> >> [hidden email]
>> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> >> Email had 2 attachments:
>> >> + dbperformance2.txt
>> >> 5k (text/plain)
>> >> + DBPerformanceTester.java
>> >> 9k (text/x-java)
>> >
>> > ------------------------------------------------------------------------------
>> > Rapidly troubleshoot problems before they affect your business. Most IT
>> > organizations don't have a clear picture of how application performance
>> > affects their revenue. With AppDynamics, you get 100% visibility into your
>> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
>> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> > _______________________________________________
>> > Hsqldb-user mailing list
>> > [hidden email]
>> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>> ------------------------------------------------------------------------------
>> Rapidly troubleshoot problems before they affect your business. Most IT
>> organizations don't have a clear picture of how application performance
>> affects their revenue. With AppDynamics, you get 100% visibility into
>> your
>> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> Pro!
>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Hsqldb-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Fred Toussi-2

Re: Fwd: HSQL/MySQL query performance

I don't know if the overhead might be reduced further. Your test is
unusual as it selects 20000 rows each time.

To ensure the tests are realistic, perform a getString() on all the rows
and columns returned by the result set to make sure actual values are
fetched.

Fred

On Thu, Dec 19, 2013, at 10:11, Viktor Ádám wrote:

> I see. There is definitely an improvement:
> Select.Walk #1: 1290 -> 669
> Select.Walk #2: 762 -> 503
> Select.Walk #3: 805 -> 487
> Select.Walk #4: 743 -> 494
> Select.Walk #5: 776 -> 493
> Select.Walk #6: 776 -> 486
> Select.Walk #7: 760 -> 488
> Select.Walk #8: 758 -> 486
> Select.Walk #9: 775 -> 487
> Select.Walk #10: 756 -> 488
>
> Do you think this is about as good as it gets? (To compare, MySQL
> produces about 150 ms execution times on these queries on this
> machine).
>
> Thanks,
> Viktor
>
> On Thu, Dec 19, 2013 at 11:02 AM, Fred Toussi
> <[hidden email]> wrote:
> > Try again. The settings are not being applied if you don't see any speed
> > difference.
> >
> > The easy way to do this is create the database with an in-process
> > connection that applies the properties then performs SHUTDOWN without
> > populating it. You then start the server without setting the database
> > properties and perform the tests. As stated before, use lowrecase for
> > peroperties.
> >
> > Fred
> >
> > On Thu, Dec 19, 2013, at 9:48, Viktor Ádám wrote:
> >> Thanks for clearing that up!
> >> However changing the code does not help on HSQL server mode speed for us.
> >>
> >> StringBuilder pathBuilder = new
> >> StringBuilder("file:/home/swn/db/testdb2");
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx).append("=").append("MVCC");
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_level).append("=").append("READ_COMMITTED");
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_conflict_rollback).append("=").append(true);
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_default_table_type).append("=").append("CACHED");
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_rows).append("=").append(100000);
> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_size).append("=").append(100000);
> >> pathBuilder.append(";").append("user").append("=").append(user);
> >> pathBuilder.append(";").append("password").append("=").append(password);
> >> server.setDatabasePath(0, pathBuilder.toString());
> >>
> >> Viktor
> >>
> >> On Thu, Dec 19, 2013 at 10:15 AM, Fred Toussi
> >> <[hidden email]> wrote:
> >> > You are not setting the properties for the database on the server
> >> > correctly. The lines starting with the ones below have no effect.
> >> >
> >> > HsqlProperties props = new HsqlProperties();
> >> > props.setProperty(HsqlDatabaseProperties.hsqldb_tx,
> >> > "MVCC");
> >> >
> >> > The properties for a server can contain only server properties. These
> >> > properties are like server.silent=false, etc.
> >> > The database properties for a server are set on the URL of the database
> >> > in this call: server.setDatabasePath(0, "file:db/testdb2"); The
> >> > properties should be lowercase.
> >> >
> >> > In addition, you should never access HSQLDB's classes that are not
> >> > documented for external use. These include org.hsqldb.DatabaseManager
> >> > etc.
> >> >
> >> > Fred
> >> >
> >> > On Thu, Dec 19, 2013, at 8:38, Viktor Ádám wrote:
> >> >> Hi Fred,
> >> >>
> >> >> thanks for your help! It is true that inserts are much faster and that
> >> >> is very impressive.
> >> >> I wasn't entirely informative about our setup. We would like to use
> >> >> HSQLDB in server mode. I've attached a new test and its result where
> >> >> we executed the sample with a server HSQLDB instance, an in-process
> >> >> instance (file:) and with MySQL. The parameters you suggested
> >> >> definitely helps in in-process mode (our select times has gone down to
> >> >> about 10 ms from about 150-200 ms). In server mode there is a slight
> >> >> improvement but it is still slower for us than MySQL.
> >> >> Do you think that it should be faster even in server mode with HSQL
> >> >> protocol?
> >> >>
> >> >> Thanks again for your help!
> >> >>
> >> >> Viktor
> >> >>
> >> >> On Thu, Dec 19, 2013 at 1:08 AM, Fred Toussi
> >> >> <[hidden email]> wrote:
> >> >> > The log shows the inserts are 3.5 times faster with HSQLDB.
> >> >> >
> >> >> > As for your SELECT statements, you are selecting all the rows in the
> >> >> > table. The default CACHE ROWS and CACHE SIZE settings for HSQLDB allow
> >> >> > fewer rows, so each time you select, lots of rows are fetched from the
> >> >> > disk.
> >> >> >
> >> >> > Just increase the above settings until SELECT speed is what you want. I
> >> >> > think it should be faster than MySQL.
> >> >> >
> >> >> > Fred
> >> >> >
> >> >> > On Wed, Dec 18, 2013, at 10:22, Viktor Ádám wrote:
> >> >> >> Hi!
> >> >> >>
> >> >> >> We are migrating our application from MySQL to HSQLDB to have greater
> >> >> >> control over the database. Everything works great except that queries
> >> >> >> are slower than they were on MySQL.
> >> >> >> I've attached a simple test running the same CREATE/INSERT/SELECT
> >> >> >> statements on HSQL and MySQL and the produced output also. In the
> >> >> >> attached output MySQL queries are 4-5x times faster and on another
> >> >> >> development machine this ratio is about 8-10x.
> >> >> >> Can you give as a hint about what we're doing wrong? Are there any
> >> >> >> configuration parameters which could be tweaked?
> >> >> >>
> >> >> >> Our test PC was:
> >> >> >> CPU: Intel Core i5-2400 @ 3.30GHz
> >> >> >> RAM: 4GB
> >> >> >> OS: Windows 7 Home Premium
> >> >> >> MySQL: 5.5.11
> >> >> >>
> >> >> >> Thank you in advance!
> >> >> >>
> >> >> >> Best regards,
> >> >> >> Viktor Ádám
> >> >> >> ------------------------------------------------------------------------------
> >> >> >> Rapidly troubleshoot problems before they affect your business. Most IT
> >> >> >> organizations don't have a clear picture of how application performance
> >> >> >> affects their revenue. With AppDynamics, you get 100% visibility into
> >> >> >> your
> >> >> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> >> >> >> Pro!
> >> >> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> >> >> _______________________________________________
> >> >> >> Hsqldb-user mailing list
> >> >> >> [hidden email]
> >> >> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >> >> Email had 2 attachments:
> >> >> >> + dbperformance.txt
> >> >> >> 3k (text/plain)
> >> >> >> + DBPerformanceTester.java
> >> >> >> 8k (text/x-java)
> >> >> >
> >> >> > ------------------------------------------------------------------------------
> >> >> > Rapidly troubleshoot problems before they affect your business. Most IT
> >> >> > organizations don't have a clear picture of how application performance
> >> >> > affects their revenue. With AppDynamics, you get 100% visibility into your
> >> >> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> >> >> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> >> > _______________________________________________
> >> >> > Hsqldb-user mailing list
> >> >> > [hidden email]
> >> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >> ------------------------------------------------------------------------------
> >> >> Rapidly troubleshoot problems before they affect your business. Most IT
> >> >> organizations don't have a clear picture of how application performance
> >> >> affects their revenue. With AppDynamics, you get 100% visibility into
> >> >> your
> >> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> >> >> Pro!
> >> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> >> _______________________________________________
> >> >> Hsqldb-user mailing list
> >> >> [hidden email]
> >> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >> Email had 2 attachments:
> >> >> + dbperformance2.txt
> >> >> 5k (text/plain)
> >> >> + DBPerformanceTester.java
> >> >> 9k (text/x-java)
> >> >
> >> > ------------------------------------------------------------------------------
> >> > Rapidly troubleshoot problems before they affect your business. Most IT
> >> > organizations don't have a clear picture of how application performance
> >> > affects their revenue. With AppDynamics, you get 100% visibility into your
> >> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> >> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> > _______________________________________________
> >> > Hsqldb-user mailing list
> >> > [hidden email]
> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >>
> >> ------------------------------------------------------------------------------
> >> Rapidly troubleshoot problems before they affect your business. Most IT
> >> organizations don't have a clear picture of how application performance
> >> affects their revenue. With AppDynamics, you get 100% visibility into
> >> your
> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> >> Pro!
> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> >> _______________________________________________
> >> Hsqldb-user mailing list
> >> [hidden email]
> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> > ------------------------------------------------------------------------------
> > Rapidly troubleshoot problems before they affect your business. Most IT
> > organizations don't have a clear picture of how application performance
> > affects their revenue. With AppDynamics, you get 100% visibility into your
> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> > _______________________________________________
> > Hsqldb-user mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into
> your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Viktor Ádám

Re: Fwd: HSQL/MySQL query performance

All right. Thank you very much for your help!

Viktor

On Thu, Dec 19, 2013 at 12:29 PM, Fred Toussi
<[hidden email]> wrote:

> I don't know if the overhead might be reduced further. Your test is
> unusual as it selects 20000 rows each time.
>
> To ensure the tests are realistic, perform a getString() on all the rows
> and columns returned by the result set to make sure actual values are
> fetched.
>
> Fred
>
> On Thu, Dec 19, 2013, at 10:11, Viktor Ádám wrote:
>> I see. There is definitely an improvement:
>> Select.Walk #1: 1290 -> 669
>> Select.Walk #2: 762 -> 503
>> Select.Walk #3: 805 -> 487
>> Select.Walk #4: 743 -> 494
>> Select.Walk #5: 776 -> 493
>> Select.Walk #6: 776 -> 486
>> Select.Walk #7: 760 -> 488
>> Select.Walk #8: 758 -> 486
>> Select.Walk #9: 775 -> 487
>> Select.Walk #10: 756 -> 488
>>
>> Do you think this is about as good as it gets? (To compare, MySQL
>> produces about 150 ms execution times on these queries on this
>> machine).
>>
>> Thanks,
>> Viktor
>>
>> On Thu, Dec 19, 2013 at 11:02 AM, Fred Toussi
>> <[hidden email]> wrote:
>> > Try again. The settings are not being applied if you don't see any speed
>> > difference.
>> >
>> > The easy way to do this is create the database with an in-process
>> > connection that applies the properties then performs SHUTDOWN without
>> > populating it. You then start the server without setting the database
>> > properties and perform the tests. As stated before, use lowrecase for
>> > peroperties.
>> >
>> > Fred
>> >
>> > On Thu, Dec 19, 2013, at 9:48, Viktor Ádám wrote:
>> >> Thanks for clearing that up!
>> >> However changing the code does not help on HSQL server mode speed for us.
>> >>
>> >> StringBuilder pathBuilder = new
>> >> StringBuilder("file:/home/swn/db/testdb2");
>> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx).append("=").append("MVCC");
>> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_level).append("=").append("READ_COMMITTED");
>> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_tx_conflict_rollback).append("=").append(true);
>> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_default_table_type).append("=").append("CACHED");
>> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_rows).append("=").append(100000);
>> >> pathBuilder.append(";").append(HsqlDatabaseProperties.hsqldb_cache_size).append("=").append(100000);
>> >> pathBuilder.append(";").append("user").append("=").append(user);
>> >> pathBuilder.append(";").append("password").append("=").append(password);
>> >> server.setDatabasePath(0, pathBuilder.toString());
>> >>
>> >> Viktor
>> >>
>> >> On Thu, Dec 19, 2013 at 10:15 AM, Fred Toussi
>> >> <[hidden email]> wrote:
>> >> > You are not setting the properties for the database on the server
>> >> > correctly. The lines starting with the ones below have no effect.
>> >> >
>> >> > HsqlProperties props = new HsqlProperties();
>> >> > props.setProperty(HsqlDatabaseProperties.hsqldb_tx,
>> >> > "MVCC");
>> >> >
>> >> > The properties for a server can contain only server properties. These
>> >> > properties are like server.silent=false, etc.
>> >> > The database properties for a server are set on the URL of the database
>> >> > in this call: server.setDatabasePath(0, "file:db/testdb2"); The
>> >> > properties should be lowercase.
>> >> >
>> >> > In addition, you should never access HSQLDB's classes that are not
>> >> > documented for external use. These include org.hsqldb.DatabaseManager
>> >> > etc.
>> >> >
>> >> > Fred
>> >> >
>> >> > On Thu, Dec 19, 2013, at 8:38, Viktor Ádám wrote:
>> >> >> Hi Fred,
>> >> >>
>> >> >> thanks for your help! It is true that inserts are much faster and that
>> >> >> is very impressive.
>> >> >> I wasn't entirely informative about our setup. We would like to use
>> >> >> HSQLDB in server mode. I've attached a new test and its result where
>> >> >> we executed the sample with a server HSQLDB instance, an in-process
>> >> >> instance (file:) and with MySQL. The parameters you suggested
>> >> >> definitely helps in in-process mode (our select times has gone down to
>> >> >> about 10 ms from about 150-200 ms). In server mode there is a slight
>> >> >> improvement but it is still slower for us than MySQL.
>> >> >> Do you think that it should be faster even in server mode with HSQL
>> >> >> protocol?
>> >> >>
>> >> >> Thanks again for your help!
>> >> >>
>> >> >> Viktor
>> >> >>
>> >> >> On Thu, Dec 19, 2013 at 1:08 AM, Fred Toussi
>> >> >> <[hidden email]> wrote:
>> >> >> > The log shows the inserts are 3.5 times faster with HSQLDB.
>> >> >> >
>> >> >> > As for your SELECT statements, you are selecting all the rows in the
>> >> >> > table. The default CACHE ROWS and CACHE SIZE settings for HSQLDB allow
>> >> >> > fewer rows, so each time you select, lots of rows are fetched from the
>> >> >> > disk.
>> >> >> >
>> >> >> > Just increase the above settings until SELECT speed is what you want. I
>> >> >> > think it should be faster than MySQL.
>> >> >> >
>> >> >> > Fred
>> >> >> >
>> >> >> > On Wed, Dec 18, 2013, at 10:22, Viktor Ádám wrote:
>> >> >> >> Hi!
>> >> >> >>
>> >> >> >> We are migrating our application from MySQL to HSQLDB to have greater
>> >> >> >> control over the database. Everything works great except that queries
>> >> >> >> are slower than they were on MySQL.
>> >> >> >> I've attached a simple test running the same CREATE/INSERT/SELECT
>> >> >> >> statements on HSQL and MySQL and the produced output also. In the
>> >> >> >> attached output MySQL queries are 4-5x times faster and on another
>> >> >> >> development machine this ratio is about 8-10x.
>> >> >> >> Can you give as a hint about what we're doing wrong? Are there any
>> >> >> >> configuration parameters which could be tweaked?
>> >> >> >>
>> >> >> >> Our test PC was:
>> >> >> >> CPU: Intel Core i5-2400 @ 3.30GHz
>> >> >> >> RAM: 4GB
>> >> >> >> OS: Windows 7 Home Premium
>> >> >> >> MySQL: 5.5.11
>> >> >> >>
>> >> >> >> Thank you in advance!
>> >> >> >>
>> >> >> >> Best regards,
>> >> >> >> Viktor Ádám
>> >> >> >> ------------------------------------------------------------------------------
>> >> >> >> Rapidly troubleshoot problems before they affect your business. Most IT
>> >> >> >> organizations don't have a clear picture of how application performance
>> >> >> >> affects their revenue. With AppDynamics, you get 100% visibility into
>> >> >> >> your
>> >> >> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> >> >> >> Pro!
>> >> >> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> >> >> >> _______________________________________________
>> >> >> >> Hsqldb-user mailing list
>> >> >> >> [hidden email]
>> >> >> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> >> >> >> Email had 2 attachments:
>> >> >> >> + dbperformance.txt
>> >> >> >> 3k (text/plain)
>> >> >> >> + DBPerformanceTester.java
>> >> >> >> 8k (text/x-java)
>> >> >> >
>> >> >> > ------------------------------------------------------------------------------
>> >> >> > Rapidly troubleshoot problems before they affect your business. Most IT
>> >> >> > organizations don't have a clear picture of how application performance
>> >> >> > affects their revenue. With AppDynamics, you get 100% visibility into your
>> >> >> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
>> >> >> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> >> >> > _______________________________________________
>> >> >> > Hsqldb-user mailing list
>> >> >> > [hidden email]
>> >> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> >> >> ------------------------------------------------------------------------------
>> >> >> Rapidly troubleshoot problems before they affect your business. Most IT
>> >> >> organizations don't have a clear picture of how application performance
>> >> >> affects their revenue. With AppDynamics, you get 100% visibility into
>> >> >> your
>> >> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> >> >> Pro!
>> >> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> >> >> _______________________________________________
>> >> >> Hsqldb-user mailing list
>> >> >> [hidden email]
>> >> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> >> >> Email had 2 attachments:
>> >> >> + dbperformance2.txt
>> >> >> 5k (text/plain)
>> >> >> + DBPerformanceTester.java
>> >> >> 9k (text/x-java)
>> >> >
>> >> > ------------------------------------------------------------------------------
>> >> > Rapidly troubleshoot problems before they affect your business. Most IT
>> >> > organizations don't have a clear picture of how application performance
>> >> > affects their revenue. With AppDynamics, you get 100% visibility into your
>> >> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
>> >> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> >> > _______________________________________________
>> >> > Hsqldb-user mailing list
>> >> > [hidden email]
>> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> >>
>> >> ------------------------------------------------------------------------------
>> >> Rapidly troubleshoot problems before they affect your business. Most IT
>> >> organizations don't have a clear picture of how application performance
>> >> affects their revenue. With AppDynamics, you get 100% visibility into
>> >> your
>> >> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> >> Pro!
>> >> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> >> _______________________________________________
>> >> Hsqldb-user mailing list
>> >> [hidden email]
>> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>> >
>> > ------------------------------------------------------------------------------
>> > Rapidly troubleshoot problems before they affect your business. Most IT
>> > organizations don't have a clear picture of how application performance
>> > affects their revenue. With AppDynamics, you get 100% visibility into your
>> > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
>> > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> > _______________________________________________
>> > Hsqldb-user mailing list
>> > [hidden email]
>> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>> ------------------------------------------------------------------------------
>> Rapidly troubleshoot problems before they affect your business. Most IT
>> organizations don't have a clear picture of how application performance
>> affects their revenue. With AppDynamics, you get 100% visibility into
>> your
>> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
>> Pro!
>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Hsqldb-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user