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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/84204ee9-6404-35a5-7766-47ddab91d855%40gmail.com.
