Unfortunately we are talking about a production situation and using a h2-version that is not officially released (for whatever that means) is something we are not allowed to do. I would be very eager to check what happens with any new upcoming release.
A join would naturally work but I would also expect the subquery to result in a similar evaluation path. If there is any reason to expect otherwise we would happily use the join. We initially did a multi-join instead of the subqueries we have now. That was beyond slow. We never saw any of those queries come to completion. On Wednesday, 26 April 2023 at 11:09:58 UTC+2 Andreas Reichel wrote: > Greetings. > > As far as I understand it, Evgenji just committed a change regarding index > use in `IN()` clause. > Maybe try the very lastest GIT Master first. > > Also I wonder, why you would not use a JOIN instead of a IN() when you > have a list of FK_A from C: > > select * > from a > inner join c > on a.pk = c.fk_a > > Why use (uncorrelated?) sub-queries? > > Best regards > Andreas > > On Wed, 2023-04-26 at 02:03 -0700, Silvio wrote: > > We have some heavy queries that involve selecting records from a base > cached table A (~100K records) that satisfy a quite a number of conditions > expressed as > > A.PK [NOT] IN (...) > > on a secondary cached table B (~10M) records. Although the subqueries use > indexed columns the overall query is very slow. > > We are thinking of using a temporary memory table C that holds primary > keys of table A, evaluating the subqueries on B seperately inserting or > removing keys into table C as needed and finally having a single subquery > > A.PK NOT IN (SELECT FK_A FROM C) > > Has anoyone ever tried such an approach in H2? Is there any reason to > expect an improvement in performance in comparison to the single large > query we have now? > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/6e7570af-a74c-4e83-9560-a85cfad1e8d4n%40googlegroups.com > > <https://groups.google.com/d/msgid/h2-database/6e7570af-a74c-4e83-9560-a85cfad1e8d4n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/570f46fc-c14f-4242-8ac8-c8b3e3da9d6bn%40googlegroups.com.
