After some more experimentation, it seems that selects slow down the further I get into the dataset. For example "select * from training.ratings limit 10 offset x". With x = 1000, it returns the rows immediately. x=10000 takes a second or two. x=10000 takes maybe 10 seconds. x=1000000 never seems to return. I end up having to kill -9 the server process and then when it re-starts it has to repair the DB which takes a few hours each time. Would partitioning help? Or is this dataset simply too big for H2 on commodity hardware?
On Sat, Jan 3, 2009 at 12:26 PM, Limbic System <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
