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.
