[prev in list] [next in list] [prev in thread] [next in thread]
List: hsqldb-user
Subject: Re: [Hsqldb-user] Long Time taken to read data: is it normal?/ Error
From: "fredt" <fredt () users ! sourceforge ! net>
Date: 2009-04-01 11:55:39
Message-ID: DD926B377D17478F8619C80EFCEB2639 () BLACK
[Download RAW message or body]
You should do your timing tests yourself with your data. Other people cannot
do this for you..
The time for the second select depends a lot on the indexes. Use of indexes
is described in the Guide.
If you execute the second select for each row returned for the first select,
it naturally takes a very long time.
Fred
----- Original Message -----
From: "indianscorpio" <cool.scorpio84@gmail.com>
To: <hsqldb-user@lists.sourceforge.net>
Sent: 01 April 2009 12:48
Subject: Re: [Hsqldb-user] Long Time taken to read data: is it normal?/
Error
I think i didnt put the question correctly
See the point I am trying to make is-
presently i have boiled down to 2 very simple statements
SELECT fld1, fld2, fld3, min(fld4) from table1 group by fld1, fld2, fld3";
Select f11, f12, f13, f14, f15, f16 from table2 where f11= f1 and f12=f2 and
f13 <= f4 group by f11, f12, f13, f14, f15, f16
(f1, f2, f4 are values from 1st select statement)
I understand that group by etc must have caused some overload but as you
said in your 1st answer to this thread it shouldn't have taken days... but I
cant understand what is making it take so much time. Individual select
statement on the table haven't take more than 15-20 mins
So what I am asking is that may be somewhere my configurations are messing
it all up or is it normal that this much time is taken in hsqldb. The answer
will be one of the two (i believe) because I can't simplify my sql anymore (
leaving aside reading small db etc) and i can't think of nay other reasons.
I hope I was clear this time
fredt wrote:
>
> The time to read the data in ATABLE once is the amount of time it takes to
> run the following query.
>
> SELECT COUNT(*) FROM ATABLE
>
> Your queries may be doing other things in addition and this adds to the
> time.
>
> ----- Original Message -----
> From: "indianscorpio" <cool.scorpio84@gmail.com>
> To: <hsqldb-user@lists.sourceforge.net>
> Sent: 01 April 2009 12:24
> Subject: Re: [Hsqldb-user] Long Time taken to read data: is it normal?/
> Error
>
>
>
> Well I ran the query with 1st RS being small and it seems to be running
> fine.. for now..
> meanwhile I want to know that from the docs it looks hsqldb can handle
> large
> db & this was the one of the reasons of zeroing on hsql. Now as per our
> design we are reading rs inside rs and both of them are too large but
> still
> isn't the time taken too MUCH?? Logically speaking and those of you who
> are
> hsqldb guys - 1st RS oh 1 million rows (8 column - varchars) & 2nd of
> approx
> 5-6 million (16 column - varchars) - how much time should have been taken
> ideally (max heap ~1.5 GB).
>
> Are there any suggestion to improve the performance .. some how???!!!!
>
> And as fredt said breaking into 5-6 different parts is fine but what if I
> want to do it in 1 go... ???
>
> BTW fredt thanks for all the help :)
>
>
> fredt wrote:
>>
>> The memory problem is clearly due to the first select. You do not need to
>> select all the rows at the same time. Just select a samll part, perform
>> the
>> operation, and select the next small part, and so on.
>>
>> ----- Original Message -----
>> From: "indianscorpio" <cool.scorpio84@gmail.com>
>> To: <hsqldb-user@lists.sourceforge.net>
>> Sent: 31 March 2009 12:33
>> Subject: Re: [Hsqldb-user] Long Time taken to read data: is it normal?/
>> Error
>>
>>
>>
>> Hi Brend
>>
>> Thanks for the info I went through your thread. Here are my doubts -
>> - I was already working with 8 Gb cache memory in .property file. With
>> indexing etc. .data file size ~3 GB
>> - I am -Xmx= 1500m (~1.5 GB) [its maximum i am able]
>> - one data set ~2 million (2,000,000) & another ~7-8 million
>> - if I just read dataset1 it takes hardly 3-4 mins
>> dataset2 2nd one ~ 12 mins
>> - no of rows you were dealing with were 608 million i.e (608,000,000)
>> ?????
>> time for you approx??
>> - can you approximately say the time in my case - read table1 and then
>> loop
>> over the result set to get corresponding values in table2 (see previous
>> post
>> for query I am using)
>>
>> @fredt: i'll be trying after adding one more condition now. But I
>> couldn't
>> understand how will it dramatically effect the memory cause 2nd result
>> set
>> may be a little small after adding some condition but I am just executing
>> that query not even doing any operation for the 2nd rs as of now. Anyways
>> will mail once this operation completes
>>
>>
>> Bernd Ritter-2 wrote:
>>>
>>>
>>> Hi,
>>>
>>> it could work, but you should really increase your heap memory. I had
>>> some
>>> similar issue, maybe you want to look up this thread:
>>>
>>> http://sourceforge.net/forum/forum.php?thread_id=2237843&forum_id=73673
>>>
>>> Bernd
>>>
>>>
>>>> From: fredt@users.sourceforge.net
>>>> To: hsqldb-user@lists.sourceforge.net
>>>> Date: Mon, 30 Mar 2009 15:18:30 +0100
>>>> Subject: Re: [Hsqldb-user] Long Time taken to read data: is it normal?/
>>>> Error
>>>>
>>>> The memory that is needed depends on the contents of the tables.
>>>> Selecting
>>>> 10M rows will certainly need a huge heap.
>>>>
>>>> As I said in my first reply, you should reduce the number of rows
>>>> returned
>>>> by the SELECT statement by adding a condition.
>>>>
>>>> >From this, you will get an idea how much space is required.
>>>>
>>>> You can test the second query with some test data, without running the
>>>> first
>>>> query. This will show you how much memory the second query needs.
>>>>
>>>> Fred
>>>>
>>>>
>>>> ----- Original Message -----
>>>> From: "indianscorpio" <cool.scorpio84@gmail.com>
>>>> To: <hsqldb-user@lists.sourceforge.net>
>>>> Sent: 30 March 2009 14:30
>>>> Subject: Re: [Hsqldb-user] Long Time taken to read data: is it normal?/
>>>> Error
>>>>
>>>>
>>>>
>>>> By a new index i just was giving a info :)
>>>>
>>>> I just wish to know vaguely with DB of such a size (20,00,000 in one &
>>>> approx 5 time of this in second rows & fields(varchar) ~ 8+8 in both)
>>>> and
>>>> both RS open how much heap is required. (I have increased it to 1500m
>>>> which
>>>> was maximum available and again running it
>>>> (approx size of .data file is 2-25 GB without indexing.)
>>>>
>>>> Again when I am using just 'Select * from table2' (1,00,00,000 rows) it
>>>> gives outOfMemory and seeing
>>>> the docs of hsqldb i dont think too large java heap is required for it
>>>> or
>>>> is
>>>> it...??
>>>>
>>>>
>>>> fredt wrote:
>>>> >
>>>> > When you get out of memory errors, the only solution is to increase
>>>> the
>>>> > heap
>>>> > memory.
>>>> >
>>>> > Adding a new index should not reduce the number of rows you can
>>>> select.
>>>> >
>>>> > In your program you are keeping a result set open and then creating a
>>>> > second
>>>> > result set. This means memory for both result sets is needed.
>>>> >
>>>> > Fred
>>>> >
>>>> > ----- Original Message -----
>>>> > From: "indianscorpio" <cool.scorpio84@gmail.com>
>>>> > To: <hsqldb-user@lists.sourceforge.net>
>>>> > Sent: 30 March 2009 13:02
>>>> > Subject: Re: [Hsqldb-user] Long Time taken to read data: is it
>>>> normal?/
>>>> > Error
>>>> >
>>>> >
>>>> >
>>>> > I added one more index and after that .data file now reaches ~5Gb.
>>>> >
>>>> > Now I am trying to read the table2 just using DB Manager and I am
>>>> getting
>>>> > OutOfMemory Error (have tried it 4-5 times S1000 error). this is
>>>> weird
>>>> > because earlier also I have tried same thing using DB manager and it
>>>> > returned answer
>>>> >
>>>> > I have earlier executed String read = "SELECT fld1, fld2, fld3,
>>>> min(fld4)
>>>> > from table1 statment and it used to take ~ 3min. Even table2
>>>> individually
>>>> > has taken <15-20 mins (select * from table2). But reading table2 from
>>>> > table1
>>>> > have not worked ever till now.
>>>> > Besides that this problem it is working a little arbitrarily (same
>>>> > statment
>>>> > sometimes works fine and sometines give this heap error). Does
>>>> creating
>>>> > more
>>>> > index be a reason that the select statement doesn't works?
>>>> >
>>>> >
>>>> >
>>>> > fredt wrote:
>>>> >>
>>>> >>>From the queries, it is not clear what causes the long query times.
>>>> The
>>>> >> quries should take a long time, but not days.
>>>> >>
>>>> >> For testing, you can add some conditions to the queries to reduce
>>>> the
>>>> >> expected size of the result and check the times.
>>>> >>
>>>> >> In general, you cannot have result sets that are larger than Java
>>>> heap
>>>> >> space.
>>>> >>
>>>> >> Fred
>>>> >>
>>>> >>
>>>> >> ----- Original Message -----
>>>> >> From: "indianscorpio" <cool.scorpio84@gmail.com>
>>>> >> To: <hsqldb-user@lists.sourceforge.net>
>>>> >> Sent: 30 March 2009 10:32
>>>> >> Subject: [Hsqldb-user] Long Time taken to read data: is it normal?/
>>>> Error
>>>> >>
>>>> >>
>>>> >>
>>>> >> Time till now: more than 3 Days (still running)
>>>> >> Size of table1: 2355459 rows, 9 fields, all varchar
>>>> >> Size of table2: approx 5 time the table1 (Today I want even able to
>>>> read
>>>> >> rhe
>>>> >> data from table2 - getting error OutOfMemory- Java Heap Space)
>>>> >> have done indexing and total db size after indexing is (.data file)
>>>> >> 3713544192 Bytes (~> 3 GB)
>>>> >> Tables created were cached tables
>>>> >> code:
>>>> >> String read = "SELECT fld1, fld2, fld3, min(fld4) from
>>>> table1
>>>> >> group by fld1, fld2, fld3";
>>>> >> int count = 0;
>>>> >> statmnt.execute(read);
>>>> >> ResultSet rs = statmnt.getResultSet();
>>>> >> while (rs.next()) {
>>>> >> String f1= rs.getString(1);
>>>> >> String f2= rs.getString(2);
>>>> >> String f3= rs.getString(4);
>>>> >> String read_2 = "Select f11, f12, f13, f14, f15, f16 from table2
>>>> where
>>>> >> f11
>>>> >> =
>>>> >> f1 and f12=f2 and f13 <= f3
>>>> >> group by f11, f12, f13, f14, f15, f16";
>>>> >> count++;
>>>> >> if (count == 10000) {
>>>> >> DateFormat dateFormat = new
>>>> >> SimpleDateFormat("yyyy/MM/dd
>>>> >> HH:mm:ss");
>>>> >> java.util.Date date = new java.util.Date();
>>>> >> System.out.println(dateFormat.format(date));
>>>> >> count = 0;
>>>> >> }
>>>> >> statmnt.executeQuery(read_2);
>>>> >> }
>>>> >> statmnt.execute("SHUTDOWN");
>>>> >> --
>>>> >> View this message in context:
>>>> >>
>>>> http://www.nabble.com/Long-Time-taken-to-read-data%3A-is-it-normal---Error-tp22779321p22779321.html
>>>> >> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>>>> >>
>>>> >>
>>>> >>
>>>> ------------------------------------------------------------------------------
>>>> >> _______________________________________________
>>>> >> Hsqldb-user mailing list
>>>> >> Hsqldb-user@lists.sourceforge.net
>>>> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>>> >>
>>>> >>
>>>> >>
>>>> ------------------------------------------------------------------------------
>>>> >> _______________________________________________
>>>> >> Hsqldb-user mailing list
>>>> >> Hsqldb-user@lists.sourceforge.net
>>>> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>>> >>
>>>> >>
>>>> >
>>>> > --
>>>> > View this message in context:
>>>> >
>>>> http://www.nabble.com/Long-Time-taken-to-read-data%3A-is-it-normal---Error-tp22779321p22781688.html
>>>> > Sent from the HSQLDB - User mailing list archive at Nabble.com.
>>>> >
>>>> >
>>>> >
>>>> ------------------------------------------------------------------------------
>>>> > _______________________________________________
>>>> > Hsqldb-user mailing list
>>>> > Hsqldb-user@lists.sourceforge.net
>>>> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>>> >
>>>> >
>>>> >
>>>> ------------------------------------------------------------------------------
>>>> > _______________________________________________
>>>> > Hsqldb-user mailing list
>>>> > Hsqldb-user@lists.sourceforge.net
>>>> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>>> >
>>>> >
>>>>
>>>> --
>>>> View this message in context:
>>>> http://www.nabble.com/Long-Time-taken-to-read-data%3A-is-it-normal---Error-tp22779321p22782926.html
>>>> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> _______________________________________________
>>>> Hsqldb-user mailing list
>>>> Hsqldb-user@lists.sourceforge.net
>>>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> _______________________________________________
>>>> Hsqldb-user mailing list
>>>> Hsqldb-user@lists.sourceforge.net
>>>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>>
>>> ------------------------------------------------------------------------------
>>>
>>> _______________________________________________
>>> Hsqldb-user mailing list
>>> Hsqldb-user@lists.sourceforge.net
>>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>>
>>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Long-Time-taken-to-read-data%3A-is-it-normal---Error-tp22779321p22803267.html
>> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>>
>>
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> Hsqldb-user mailing list
>> Hsqldb-user@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>>
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> Hsqldb-user mailing list
>> Hsqldb-user@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Long-Time-taken-to-read-data%3A-is-it-normal---Error-tp22779321p22824415.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>
--
View this message in context:
http://www.nabble.com/Long-Time-taken-to-read-data%3A-is-it-normal---Error-tp22779321p22824794.html
Sent from the HSQLDB - User mailing list archive at Nabble.com.
------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic