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