Replacing AND combination with UNION of SELECT queries seems to improve the run time dramatically.
On Oct 16, 11:55 am, Michael <[email protected]> wrote: > Hi, > > I have a query, which runs 80 seconds on H2 1.3.x, while in 1.2.x the > same query takes less than a second. How is that possible? > > The query is: > > "DELETE FROM sw_stream_resources WHERE id NOT IN(SELECT resource_id > FROM sw_stream_resource_assoc) AND id NOT IN(SELECT resource_id FROM > sw_resource_scenario_assoc)" > > 'id' is a primary key in 'sw_stream_resources', and there are indices > on 'resource_id' in 'sw_stream_resource_assoc' and in > 'sw_resource_scenario_assoc'. > > EXPLAIN ANALYZE prints the following: > > DELETE FROM PUBLIC.SW_STREAM_RESOURCES > /* PUBLIC.SW_STREAM_RESOURCES.tableScan */ > /* scanCount: 1544 */ > WHERE (NOT (ID IN( > SELECT > RESOURCE_ID > FROM PUBLIC.SW_RESOURCE_SCENARIO_ASSOC > /* PUBLIC.SW_RESOURCE_SCENARIO_ASSOC.tableScan */ > /* scanCount: 103 */))) > AND (NOT (ID IN( > SELECT > RESOURCE_ID > FROM PUBLIC.SW_STREAM_RESOURCE_ASSOC > /* PUBLIC.SW_STREAM_RESOURCE_ASSOC.tableScan */ > /* scanCount: 14990 */))) > /* > total: 1367 > PAGE_INDEX.PAGE_INDEX_DATA read: 1 (0%) > SW_STREAM_RESOURCES.SW_STREAM_RESOURCES_DATA read: 714 (52%) > SW_STREAM_RESOURCE_ASSOC.SW_STREAM_RESOURCE_ASSOC_DATA read: 578 (42%) > overflow read: 74 (5%) > */ > > Thanks, > Michael -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
