Hello dear HSQL-fans!
We're using HSQL since more than one year and are really happy. Anyway, one of our most utilized databases already became quite slow. So we created two JDBC connections, one to the old and one to a new database and copied one value after the other to the new database. The new database was MUCH faster, most probably because the index was recreated.
So we have two questions:
1) Is there some kind of indicator that tells the "health" of the index (e.g. is it balanced or whatever)
2) Does the same approach work when we copy within the database from the original table to a temporary and then switch original and temporary?
Thanks a lot in advance!
The new database became faster because the indexes were recreated and the data was compacted.
You can achieve the same result simply by executing SHTUDOWN COMPACT or CHECKPOINT DEFRAG on the original database.
The INFORMATION_SCHEMA.SYSTEM_CACHEINFO has an indicator for lost spaces. When this value is a high percentage of the .data file size a compacting operation should speedup database access.
Thanks for the reply - I tried to use the SYSTEM_CACHEINFO but honestly saying, I don't know how to use that info. The most important changes between the "uncleaned" and the "cleaned" database are in the CACHE_BYTES column. The FILE_LOST_BYTES is 0 in both databases. Is there some info per table to know if the index is balanced (or whatever speeds up the database)
The SYSTEM_CACHINFO does not show spaces lost due to DROP TABLE or ALTER TABLE etc.
If your databases will get large, you can subscribe to SupportWare and get extra help with managing them.
After a few tests, I found out that DROP INDEX and CREATE INDEX are already enough to speed up the access time dramatically. From my interpretation of the source code, the index is a b-tree which should auto-balance. Could it be that there is a bug in this implementation (maybe fixed in 2.3.0?)
Thanks a lot
Both CREATE INDEX and DROP INDEX cause the data for the whole table to be rewritten sequentially, resulting in speedup of the specific table. But the old space is lost, resulting on larger overall file size.
HSQLDB indexes are not B-tree and there is no bug.