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
<[email protected]> 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
>> <[email protected]> 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
>> >> <[email protected]> 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
>> >> >> [email protected]
>> >> >> 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
>> >> > [email protected]
>> >> > 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
>> >> [email protected]
>> >> 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
>> > [email protected]
>> > 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
>> [email protected]
>> 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
> [email protected]
> 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