Hello, Konstantin. > I think we cannot just turn this optimization on
Why is that? If we turn on `OPTIMIZE_REUSE_RESULT` consequences would be the following: * concurrent updated of the subquery table will not be used(we have no guarantee on that, for now) * performance of the select increase due to the subquery cache. > And incrementing org.h2.engine.Database#modificationDataId on each data > update may lead to some performance issues. It should be benchmarked first. For now, Ignite doesn’t guarantee the visibility of concurrent update into select results [1] [2] > It should be benchmarked first. +1 What have I missed? [1] https://apacheignite-sql.readme.io/docs/how-ignite-sql-works#subqueries-in-where-clause [2] https://apacheignite-sql.readme.io/docs/how-ignite-sql-works#concurrent-modifications > 22 апр. 2020 г., в 10:39, Ivan Daschinsky <ivanda...@gmail.com> написал(а): > > Thank you for your response, Konstantin > > I think we cannot just turn this optimization >> > Of course not, that is the reason why I started this thread. > > org.apache.ignite.internal.processors.query.h2.opt.GridH2Table don't change >> it neither on remove or update > > > Yes, but we can increment this counter and find possible workarounds. > > It should be benchmarked first. >> > Absolutelly agree. > > I started this thread to discuss possible caveats and traps and decide > whether to implement this optimization or not. > If there is too many traps or moreover this is impossible or SQL > maintainers are against this, then it's probably not worth to do it. > > > > ср, 22 апр. 2020 г. в 09:52, Konstantin Orlov <kor...@gridgain.com>: > >> Hello, Ivan >> >> I think we cannot just turn this optimization on because a result >> invalidation counts on org.h2.engine.Database#modificationDataId (see >> org.h2.command.dml.Query#sameResultAsLast(Session, Value[], Value[], >> long)), but org.apache.ignite.internal.processors.query.h2.opt.GridH2Table >> don't change it neither on remove or update. And incrementing >> org.h2.engine.Database#modificationDataId on each data update may lead to >> some performance issues. It should be benchmarked first. >> >> -- >> Regards, >> Konstantin Orlov >> >> >>> On 21 Apr 2020, at 14:51, Ivan Daschinskiy <ivanda...@gmail.com> wrote: >>> >>> Hello folks. >>> >>> Recently I trying to investigate why when the query, i.e. «SELECT * FROM >> T1 WHERE ID IN (SELECT T1_ID FROM T2), is executed locally, >>> subquery evaluated, even if result is the same. I noticed, that in >> mentioned in subj (.ConnectionManager#DEFAULT_DB_OPTIONS), >>> parameter OPTIMIZE_REUSE_RESULT explicitly set to 0. This value disable >> internal H2 optimization (see details here >> org.h2.command.dml.Query#query(int, org.h2.result.ResultTarget) and lead >> the mentioned above query to be ineffective. >>> >>> I cannot understand why this decision was made. Also I don’t find any >> evidence that setting OPTIMIZE_REUSE_RESULT to 1 could break something. >> Unfortunatelly, it is impossible to change this behavior per Session >> without forking H2, because this flag is set in org.h2.engine.Database. >>> >>> Let’s discuss possible caveats in enabling this optimization by default >> in DEFAULT_DB_OPTIONS. >>> >> >> > > -- > Sincerely yours, Ivan Daschinskiy
