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?
