Thomas,
I'm beginning to think there is a more general problem, as I'm seeing
this kind of slowness on very simple queries, such as "select * from
training.ratings" in the H2 console, with max rows set to 1000. I
also tried creating the index as you suggested... it ran overnight
before completing, and does not seem to have improved things.
I'm starting the server like this:
java -Xmx512m -cp lib/h2.jar org.h2.tools.Server -web -browser -tcp
and then issuing my SQL from the browser console. All of this is with
H2 1.1.105 (2008-12-19) running on a Mac with Java 1.5.
Many thanks for your help.
On Sat, Jan 3, 2009 at 6:34 AM, Thomas Mueller
<[email protected]> wrote:
>
> Hi,
>
> Yes, you should try to convert your query to a inner join.
>
> Also, you should create an index on training.ratings.book_id
>
> What version of H2 do you use? With version 1.1.x it should run fast.
>
> Regards,
> Thomas
>
>
>
> On Wed, Dec 31, 2008 at 3:58 AM, Dom <[email protected]> wrote:
>>
>> Try it like this...I created your tables and this at least ran:
>>
>>> select a.customer_id from
>>> ( select customer_id from training.ratings where book_id in
>>> ( select book_id from training.ratings where customer_id= 5 )
>>> order by customer_id
>>> ) as a
>>
>> The difference is that your first compares a complete result set to a
>> complete result set, resulting in a...I dunno, a cartesian product I
>> think, and I can see how this query could be written with a JOIN,
>> which may be more efficient. But your sub-select makes a selection
>> from a result set...treats the result set as the DB object you're
>> selecting from. So it needs an alias...I think.
>>
>> See if this or something like it could work for you instead (it does
>> run for me against your tables):
>>
>> SELECT a.customer_id,a.book_id,b.customer_id FROM training.ratings AS
>> a
>> INNER JOIN training.ratings AS b ON a.book_id = b.book_id
>> WHERE b.customer_id = 5
>>
>>
>>
>> On Dec 30, 5:44 pm, "[email protected]" <[email protected]>
>> wrote:
>>> Hi all,
>>>
>>> I'm having a trouble with H2 getting stuck on a sub-query. If I do
>>> the following, it returns very quickly with my results:
>>>
>>> select customer_id from training.ratings where book_id in
>>> ( select book_id from training.ratings where customer_id = 5 )
>>> order by customer_id
>>>
>>> However if I embed this query into another query, it hangs:
>>>
>>> select customer_id from
>>> ( select customer_id from training.ratings where book_id in
>>> ( select book_id from training.ratings where customer_id= 5 )
>>> order by customer_id
>>> )
>>>
>>> My tables are created with the following:
>>>
>>> create schema training
>>> create table training.customers (id int primary key);
>>> create table training.books (id int primary key, name varchar,
>>> date date);
>>> create table training.ratings (customer_id int not null, book_id int
>>> not null, date date not null, rating real, primary key (customer_id,
>>> book_id));
>>>
>>> Any help appreciated, thanks.
>> >
>>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
