I am experiencing a dramatic performance hit with a subquery utilizing
a "IN" predicate. The query involves a total of 4 tables. If I run
the query (with subquery) it takes ~4.5sec to complete. If I execute
the subquery by itself, it takes ~0.03sec to complete. If I then copy
the results of the
-- This is the real query (with subquery)
SELECT em.end_item_meter_id
FROM end_item_meter em
WHERE em.end_Item_meter_id IN
(
SELECT
mr.end_item_meter_id
FROM
Meter_Reading mr
Andrew Alsup wrote:
Is it possible to rewrite this as a top-level join, without using a subquery?
Something along the lines of:
SELECT em.end_item_meter_id
FROM end_item_meter em
inner join Meter_Reading mr
on em.end_Item_meter_id = mr.end_item_meter_id
INNER JOIN E
Jorgen Loland wrote:
>You can try to rewrite the query to use EXISTS instead of IN. I'm not
>sure if Derby already does this transformation for your query, but you
>can try to print the query execution plan to check.
>
>Try if this gives you the correct answer:
>
>SELECT em.end_item_meter_id
>FROM
Does it help if you first SELECT the inner rows into a temporary
table, and then do a SELECT * from that tmp table in the IN subquery?
Dag
> Is it possible to rewrite this as a top-level join, without using a subquery?
>
> Something along the lines of:
>
> SELECT em.end_item_meter_id
> FROM end_item_meter em
> inner join Meter_Reading mr
> on em.end_Item_meter_id = mr.end_item_meter_id
> INNER JOIN End_Ite
