I wanted to use HSQL DB Text table for querying on flat files. I had no problem to start using it, documentations were very helpful.
My files were all huge files and can go upto few million records. I initially tried with small volumne file and performance was good.
When I tried with a file sized (100K rows x 50 columns), I started having huge performance problem and I am writing this to know if I am doing anything wrong.
My experience with large file (100K x 50 cols)
(1) Aggregate functions on (100K rows x50 cols) file takes about 20 -30 mins.
(2) Tried indexing - Each index creation takes about 30 mins, so if I have to query all 50 cols (which is what i have to do) then index creation itself would take about 25 hrs (extrapolated value).x
(3) When ever I connect to the file with Text table, each time it takes about 30 mins.
(4) If I have any text table in the database, server start time will be after 30 - 40 mins.
Is this the expected performance on (100K x 50 cols) files and my file contains all Numeric (19,7) data. Just to add, I tried running the server with memory parameters set to 1GB(Xms & Xmx values) and that doesnt help as well.
Any help or suggestion on this would be very helpful.
Machine config - 2 GB Ram & Windows Vista OS
Thanks and Regards,
Karthick | Mob: +44 7502233737
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful.