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: Derby 10.5.3: subquery performance using "IN"
Date Sun, 15 Nov 2009 15:39:50 GMT
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   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
>     );


I just compared the query using "IN" and "EXISTS".
The original "IN" query took a total of 5.244 sec to complete.
The "EXISTS" query took a whopping 305.567 sec to complete!

Thanks for the suggestion, but I don't think that's gonna be an option.

Any other ideas?

Mime
View raw message