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/84204ee9-6404-35a5-7766-47ddab91d855%40gmail.com.
