Salut Sébastien, I understand that it's a huge amount of data, so I do expect some slowdown... I just didn't expect it to be linear, especially not for things like "select count(*)". I am loading my data into a PostgreSQL instance to see how it compares for such operations.
Thanks. On Tue, Jan 6, 2009 at 6:00 PM, Sebastien <[email protected]> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
