Hi, I think optimising OFFSET is possible, but to really optimise it more significantly, we would need to avoid doing unnecessary I/O. Just avoiding the in-memory transformation wouldn't really help that much in my view, and possibly would make the code more complex. But to avoid doing the I/O with the current data structures is quite hard. And I don't think MySQL uses significantly different data structures (I don't think they use a counted b-tree as in the MVStore), so I'm a bit surprised that MySQL is faster. But maybe MySQL isn't actually all that much faster - possibly it's just because MySQL has more cache by default: the cache size of H2 is usually 16 MB, and for MySQL it's a lot more I believe. In any case, before we do optimise anything, we would need a good benchmark, and we would need to understand why MySQL is faster.
Another question of course is: is it a high priority to optimise OFFSET? With high priority I mean, does it make sense to delay other work and concentrate on this. This I don't know, but I believe it's not such a high priority. Of course others might feel different :-) If so, please speak up! Regards, Thomas On Fri, Jul 12, 2013 at 4:03 PM, Noel Grandin <[email protected]> wrote: > > On 2013-07-12 15:53, Lukas Eder wrote: > >> >> OK, I'm going to be a bit sarcastic in this mail. I hope you're not >> offended. >> >> Not at all. > > > Now, if I specify OFFSET 600000 and H2 will evaluate the projection for >> all the 599999 records that are not of interest, then OFFSET might be a bit >> slow, right? Consider this code from Select.queryFlat(). 600000x the >> following piece of code >> >> You are correct, that it is how we currently perform that query. > > > >> That's 600000 arrays put into the result arraylist, each with the length >> of the number of columns in the queried table. I don't know if this >> accounts for 10% or 90% of the OP's reported 20 seconds. But 600000 arrays >> with 17 columns (from the OP's table) is a lot of wasted memory when >> skipping to OFFSET 600000. >> > You say waste, I say nice simple code. > Now, I'm not saying you're wrong, and maybe we can improve that. > But I'd like to see a real-world case first, and I'd also like to see some > profiling that points to that code as a problem. > Because at the moment our performance is pretty darn good, and a large > chunk of that is precisely because we don't go chasing benchmarks and > fixing problems that are only theoretical. > > > > >> And this happens for non-silly queries, too. In fact, this happens for >> every OFFSET clause unless I made a mistake? OFFSET 1000 is a more >> real-world use-case, agreed. And as a side-effect of this issue, there was >> also the wrong (or unexpected) FOR UPDATE behaviour. >> > Yeah, the side effect is that we lock a few more rows than we should. > But that's not a correctness issue, it's just a performance issue. > > And this is my last word on this topic for today, further queries will be > > /dev/null > > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to > h2-database+unsubscribe@**googlegroups.com<h2-database%[email protected]> > . > To post to this group, send email to [email protected]. > Visit this group at > http://groups.google.com/**group/h2-database<http://groups.google.com/group/h2-database> > . > For more options, visit > https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> > . > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
