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 -~----------~----~----~----~------~----~------~--~---
