2013/7/12 Noel Grandin <noelgran...@gmail.com> > > On 2013-07-12 09:03, Lukas Eder wrote: > >> In exceptional cases, OFFSET clauses can be most efficiently implemented >> by counting and skipping the largest possible logical data sets in either a >> TABLE or INDEX structure, according to the specified SELECT query. >> >> It should be possible to pass this information down to our Index classes > and have them perform the skipping. > The downside is that this optimisation is only applicable to top-level, > single-table queries. > Anything else would have to fall back to the current implementation. > > Given that the OP was asking about a synthetic benchmark, not a real-world > use-case, I agree that it is not worth the extra complexity. >
It is a real-world use-case, in my opinion. :-) Any database visualisation tool that has a paging feature will produce precisely those queries... This may include something as simple as a website. It's just very probable that predicates will be added to the query. I'm actually wondering if the code in Select.queryFlat() is correct, with respect to the FOR UPDATE clause. It seems that LIMIT 3 OFFSET 2 will lock 5 rows, instead of 3. So maybe, the performance improvement would really be to push offset handling into queryFlat() and skip the evaluation of the projection and the locking for skipped rows. Of course, I'm not sure about potential side-effects of such a change... -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
