Hi, Any news on this? Please let me know if you need more information, or if there is something else I could do to help, thanks.
On Jan 3, 11:00 pm, "Limbic System" <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
