HSQLDB slow Select query

Help
ecu
2014-08-18
2014-08-20
  • ecu
    ecu
    2014-08-18

    Hello,

    I am using hsqldb full in memory for a project and I need fast select queries for the project. I made some tests to measure the performance as below;

    I am using HSQLDB as embedded and full in-memory. I have a table with 92 columns and insert one million rows of that table to the database. Inserting performance is relatively good in order of miliseconds. However, Select or Select Like query as given below of a row from database takes 3-4 seconds which is very high for the project needs.

    -SELECT column1,column2,column3 FROM table WHERE column1 LIKE '%xxx';
    -SELECT column1,column2,column3 FROM table WHERE column1= 'xxxxxx';

    In the project, I will have huge database and it should perform fast and high amount of select queries(~in miliseconds) while the new rows being inserted continously to the database.

    Can you help me about this issue? What am i doing wrong and How can i achieve this performance?

     
    Last edit: ecu 2014-08-18
  • Fred Toussi
    Fred Toussi
    2014-08-18

    The SELECT with column1='xxxxx' will return in nanoseconds if you create an index on the column.

    The LIKE '%XXX' pattern cannot use the index whereas LIKE 'XXX%' can use the index.

     
  • ecu
    ecu
    2014-08-19

    I tried using index. Sorry, I forgot to mention but 3-4 seconds is the query time when i use index actually.

     
  • Fred Toussi
    Fred Toussi
    2014-08-19

    In 3 seconds it should return many thousands of rows. If you do have an index on the column and only a few rows are returned, the query time should be below 1 millisecond.

     
  • ecu
    ecu
    2014-08-19

    Hello, Thanks for the answer.
    I realized that there was a mistake in my indexing, so the performance is much more better now, in order of hundred microseconds.

    Is there any suggestion to increase the speed to the nanoseconds ?

     
    Last edit: ecu 2014-08-19
  • Fred Toussi
    Fred Toussi
    2014-08-19

    If there is some heap memory left when you perform the select, it should take less than 1 millisecond to return a few rows.

     
  • ecu
    ecu
    2014-08-19

    Yes, it takes lower than 1 miliseconds, it takes about 0.1, 0.2 miliseconds.

    Thanks for the answer. I need a query time lower than 0.1 miliseconds, is it achievable?

     
  • Fred Toussi
    Fred Toussi
    2014-08-19

    It will perform faster if you reuse a PreparedStatement for multiple queries. Java JIT compiler speeds up after thousands of executions. Other than that, it is the computer speed.

     
  • ecu
    ecu
    2014-08-20

    ok, thank you for the help :)