I just check myself. I have a fact table which contains 10M rows (4 integer, one double, primary index on 3 integer). So very similar.
A table scan with JDBC ( embedded ) takes around 60 seconds. Just making a sum takes 30 sec. The difference is due to the creation of a temp table for the result as there is no server-side cursor. Limit and offset come after this temp table is created. Not exactly because it read only offset+limit row (thus a linear decreasing of performances). But this tricks don't work if you use an order by (your first post), it will make a copy of your whole rating table (expensive), order it (very expensive). On 6 jan, 22:19, "[email protected]" <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
