Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

HSQL DB in Embeded Mode

2012-08-10
2014-01-19
  • harish alwala
    harish alwala
    2012-08-10

    Hi All
    I have a problem with large database in embedded mode.
    I java desktop application and i am using HSQLDB in embedded mode. When load the db with minimum data, the performance of the db is perfect. But when I load the db with huge amount(<dbname>.script file size = 216 er mb and <dbname>.logs file size 14 mb), the performance degrades dramatically.  Connection establishment  takes around 8 secs.
    How to resolve this issues. Can I not use the database in embedded mode if I need to store huge of data in db?
    Another doubt I have is-> If I use the application in embedded mode will the complete data in db will get loaded to application memory?
    en

     
  • Fred Toussi
    Fred Toussi
    2012-08-10

    You are using MEMORY tables. Therefore all the data is in memory. When your database fills up the memory, a lot of garbage collection happens and things slow down.

    Please read the Guide and convert your large tables to CACHED tables. After this, much less memory is used.  You will also have a .data file and your .script file will be smaller.

     
  • harish alwala
    harish alwala
    2012-08-10

    You are correct, I was not aware of Memory/cache mode. Now re-created the database it works fentastic. 
    This is what I have done.
    1. SET DATABASE DEFAULT TABLE TYPE CACHED
    2. Set the system properties at the application launch. (Interestingly if these properties are set in DBConnection url things doesnot work as expected)
    System.setProperty("hsqldb.log_data", "false");
    System.setProperty("hsqldb.reconfig_logging", "false");
    3. Since my application has too many db operations I manually calculated to get connection for every 50 DB operations.
    4. My DB URL looks like this.
    rurl = "jdbc:hsqldb:file:/" + getHSQLDBPath() + dbname + ";shutdown=true;hsqldb.cache_rows=10000;hsqldb.nio_data_file=false";
    Now my application works great.
    Thanks for the response.

     
  • Fred Toussi
    Fred Toussi
    2012-08-10

    This one will not work

    System.setProperty("hsqldb.log_data", "false");

    Use the setting on as a connection property when you create the database. You can use the alternative SQL statement on an existing database.