Re: Derby 10.5.3: subquery performance using "IN"

[Jørgen Løland]

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 End_Item_Event eie
              ON eie.end_Item_Event_Id = mr.end_Item_event_Id
     INNER JOIN End_Item_Inventory eii
              ON eii.end_Item_inventory_Id = eie.end_Item_inventory_id
     WHERE
         eii.End_Item_Inventory_Id = 6061799

Does that query produce the right results for you?

Thanks for the suggestion.  That does get the correct results for the
SELECT.  However, I really need this in an UPDATE statement.

UPDATE end_item_meter
SET end_item_id = ?
WHERE end_item_meter_id IN (... subquery ...)

I re-wrote it as a SELECT for testing and determined that it SELECT
with subquery had the same performance issue as UPDATE with subquery.
I don't think its possible to perform a JOIN on the subject table of
an update statement.  Thats why I've used subqueries in the past.

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   end_item_meter em
WHERE EXISTS
    (
    SELECT 1
    FROM
        Meter_Reading mr
            INNER JOIN End_Item_Event eie
                ON eie.end_Item_Event_Id = mr.end_Item_event_Id
            INNER JOIN End_Item_Inventory eii
                ON eii.end_Item_inventory_Id = eie.end_Item_inventory_id
    WHERE
        mr.end_item_meter_id=em.end_Item_meter_id and
        eii.End_Item_Inventory_Id = 6061799
    );

--
Jørgen Løland