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
<[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