Hi Noel, OK, I'm going to be a bit sarcastic in this mail. I hope you're not offended.
2013/7/12 Noel Grandin <[email protected]> > > On 2013-07-12 14:27, Lukas Eder wrote: > > > Why not? Why not challenge an Open Source database with a silly stress > test? > > > Because the nice part about being an Open Source database is that we can > make the choice to ignore stupid test cases that don't make real world > sense. > I take as my inspiration Linus Torvald's frequent rants against exactly > the behaviour of optimising for non-useful benchmarks. > Yes, the great inspiration to all benevolent dictators! I know the feeling. > But hey, if you want to produce patches to optimise such use cases, I > won't stand in the way of getting them into the code. > I used to want that. But Linus's rants never motivated me too much :-) > Apart from this OFFSET story, > > > I tend to ignore tack-on questions that try to hijack a discussion, > otherwise it gets painful to remember where I am in which part of the > discussion. > OK, one more time, before I give up. These were neither tack-on questions nor a hijacked discussion unless you have a very different understanding when it comes to hijacking. I was actually trying to give some input about various ways how OFFSET can be optimised. Let's re-read the OP's mail subject: "The OFFSET should be optimized in H2" So, not hijacked. :-) After giving my input about execution plans, I found an actual bug related to OFFSET handling, unless I misunderstood the code. 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 Value[] row = new Value[columnCount]; for (int i = 0; i < columnCount; i++) { Expression expr = expressions.get(i); row[i] = expr.getValue(session); } 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. 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. > I'd rather you started a new thread with a different subject line. > Maybe later, Linus. :-) BTW: I'm sorry if my comments are perceived off-topic or rude. I'm trying to take 1-2 things with a bit of humour. It wasn't meant personal. I was just trying to help. -- 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.
