db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew Alsup <bluesbrea...@gmail.com>
Subject Re: Re: Derby 10.5.3: subquery performance using "IN"
Date Tue, 10 Nov 2009 18:57:00 GMT
> 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.

Ideas?  Thanks again!

View raw message