Re: [Hsqldb-developers] Using text table for 100K rows * 50 columns

[Fred T. <fr...@us...>]

Performance of Text tables is relatively slow. This is due to two
things. First, it is relatively slow to read and parse a text
row, compared to binary rows in CACHED tables. Second, the
default amount of allocated cache is small.

You can speed it up a lot by increasing the cache for each text
table according to its size. For example, using cache_scale=16
results in about 185000 rows in the cache for the given table.

Regarding index creation, there is a better way to do it. You
first detach the source from the table by using SET TABLE x
SOURCE OFF, then create all the indexes, which takes no time,
then use SET TABLE x SOURCE ON

Fred


On Mon, 09 Nov 2009 19:10 +0530,
Kar...@co... wrote:

Hi,

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.

Question:
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 inform
 ation.
 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, prin
 ting or copying of this email or any action taken in reliance on this e-m
 ail is strictly prohibited and may be unlawful.