That is actually a very good idea. We never considered using a UNION here. Would H2 be able to use a different index for multiple queries inside a single UNION? I was under the impression H2 always uses one index for a query but that may no longer be the case. If that is true this could improve things a lot.
The suggestion about the temp table creation is also valuable. We have to account for multiple users running the same type of queries at the same time so we would need a separate table for each of them or complicate things by adding additional information to the temp table to distinguish the records for separate queries. On Wednesday, 26 April 2023 at 11:12:19 UTC+2 Noel Grandin wrote: > > > On 4/26/2023 11:03 AM, 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 (...) > > > > You could also express those as > > A.PK NOT IN ( SubNotCondition1 UNION SubNotCondition2 ... ) > AND A.PK IN ( SubCondition1 UNION SubCondition2 ... ) > > which should result in less scanning of the second table. > > > > > 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 > > > > That might help, and I have done similar things (on other databases). > > You probably want a session-local temporary table, and you probably want > to do > > CREATE IF NOT EXISTS TEMP1 > TRUNCATE TEMP1 > > for each query, rather than creating and dropping it, because CREATE/DROP > is quite expensive in H2. > > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/a5d488ea-efc1-4154-b9c1-128ed08408e5n%40googlegroups.com.
