Quantcast

Using text table for 100K rows * 50 columns

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Using text table for 100K rows * 50 columns

KSUBBA
Hi,

I wanted to use the HSQL Text table feature for querying on flat files. But my flat files are really huge and I expected the performance would be slower but it was very very slow.

My file contain 100K rows * 50 columns - All Numeric 19,7.

When I use aggregate functions like count(1), avg(field), sum (field) - Each aggregate function is taking about 20 - 30 mins to execute.

I thought of adding index but then each indexing takes about 20 - 30 mins & if I had to use aggregate functions on all columns then eventually I have to create the index for all 30 cols. And this by itself would take 15 hrs.

Wanted to know if this is the expected performance or I am doing something wrong here.

Any help on this would be very useful.

Thanks,
Karthick
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Using text table for 100K rows * 50 columns

KSUBBA
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 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.

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using text table for 100K rows * 50 columns

Fred Toussi-2
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, [hidden email] 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 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.

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
hsqldb-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
Loading...