2013/7/12 Noel Grandin <noelgran...@gmail.com>
>
> On 2013-07-12 10:07, Lukas Eder wrote:
>
>>
>> It is a real-world use-case, in my opinion. :-)
>>
>
> Go back and re-read the original email.
OK, OK ;-)
Let's go back and re-read the original email. Citing the OP:
"Just for write a database performance report and mysql faster than h2
when use similar function"
In this case, the OP did have a real-world use-case that you, as an H2
developer, should be concerned about in my opinion. They were making a
rather generic performance benchmark to compare H2 with MySQL. Now, whether
this benchmark was done well or thoroughly might be questionable, but I'd
be careful with immediately jumping to the conclusion that
"The correct answer is 'don't do that'. "
Why not? Why not challenge an Open Source database with a silly stress test?
Now, once such a benchmark surfaces blogs, indicating that H2 might be
poorly optimised, it is hard to get rid of such feelings again. Besides, if
you don't take such requests seriously, you risk facing FUD, eventually. So,
please, Noel, let me kindly ask you to be a bit more open-minded towards
such support requests. In the long run, I think they can add value to H2,
which is a great database. Even if the actual implementation may not be a
high priority just yet :-)
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.
>>
>> In which case we can't use the optimisation.
> The moment you do anything more complex than a select without any where
> clauses and only one table in the from clause you can't use the
> optimisation.
>
The Oracle database does 100's of silly optimisations, such as a constant
predicate stop check. An Oracle query or an Oracle subselect will not be
fully executed if - by query transformation - it can be proven that a part
won't return any results. An example:
SELECT * FROM t WHERE 1 = 0
Or also
SELECT * FROM t t1
INNER JOIN (
SELECT * FROM t t2 WHERE 1 = 0
) ON t1.id = t2.id
Or if there's a CHECK constraint on t.a, such as CHECK(t.a in (1, 2))
SELECT * FROM t WHERE t.a = 3
These sure aren't mission-critical optimisations, but they do add some
value, even if the above statements are overly silly. But with a
sophisticated CBO, every little optimisation eventually adds up towards
Oracle (or also SQL Server) being awesome databases. Tom Kyte's "Metadata
Matters" is an excellent presentation about the topic:
http://blog.jooq.org/2011/11/25/how-schema-meta-data-impacts-oracle-query-transformations
Now, if adding OFFSET optimisations to complex queries might be out of
scope, adding simple ones to simple queries might help those users who know
that H2 has these optimisations and who can explicitly leverage those in
their queries.
Apart from this OFFSET story, what's your take about my findings related to
bad locking of records that are not within the LIMIT .. OFFSET window? I
think there is a significant bug in Select.queryFlat(). Besides, there's a
low hanging optimisation fruit for all OFFSET handling (with or without
predicates), if the projection is never evaluated for those rows that are
excluded by the OFFSET clause. What do you think?
Cheers
Lukas
--
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.
