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?
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.
I tried using index. Sorry, I forgot to mention but 3-4 seconds is the query time when i use index actually.
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.
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 ?
If there is some heap memory left when you perform the select, it should take less than 1 millisecond to return a few rows.
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?
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.
ok, thank you for the help :)