db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jørgen Løland <Jorgen.Lol...@Sun.COM>
Subject Re: Derby 10.5.3: subquery performance using "IN"
Date Wed, 11 Nov 2009 08:00:49 GMT
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

Mime
View raw message