HSQLDB Cached Table Versus Memory Table Performance & Conversion

In short in HSQLDB cached table sucks in terms of performance. To elaborate I was running a program which takes around 9 hours running on two medium sized cached tables (bigger one 163 MB). I changed the tables to memory tables (default) and it now takes less than 10 minutes. Let’s see how we can easily convert memory table to cached table and vice-versa.

How can you convert a HSQLDB Memory Table to Cached Table?

This one is easy. I just open the script file and change the word memory to cached in the create table statement. It automatically converts it to cached table next time it is used. The reverse is however more complex.

How can you convert a HSQLDB CACHED Table to MEMORY Table?

You need to create a temporary table and copy all the data (preferably using insert into … select * from … query) to that table. Then drop the original table and then re-create it as a memory table. Now copy all the data back from the temporary table to the original table and finally drop the temporary table. While the procedure may sound complicated, it is actually a piece-of-cake to implement for any DBA, worth his salt.

Here is what I used to convert a large CACHED table to MEMORY table:

st.execute("CREATE CACHED TABLE TEMPSHEET(\"Meta ID\" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY, ...)");
st.execute("INSERT INTO TEMPSHEET (SELECT * FROM SHEET)");
st.execute("DROP TABLE SHEET");
st.execute("CREATE MEMORY TABLE SHEET(\"Meta ID\" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY, ... )");
st.execute("INSERT INTO SHEET (SELECT * FROM TEMPSHEET)");
st.execute("DROP TABLE TEMPSHEET");
st.execute("SHUTDOWN COMPACT");

Note: The above is excerpted from working Java code. The “shutdown compact” is recommended for better performance in future.

Conclusion

The bottomline is that you should use HSQLDB only in Memory Table mode, instead of CACHED tables mode. If you need CACHED table mode due to memory limitations then you should consider a different database.