3 tables: Item has 7k rows but Price and Data tables are individually approx 1gb in size with 10 million rows each.
Item- itemid,itemName
Price- itemid,itemPrice,itemDate(Date)
Data- itemid,itemPrice,itemDate(Date),Some other fields
How to get min(itemid) such that max(itemDate) from Price> max(itemDate) from Data for every itemid?
MyApproach (Working but very slow takes approx. 2 hrs for this query)
select min(itemid)
from Price p
where p.itemid in (select itemid from Item)
and p.itemDate > (select max(itemDate) from Data d where d.itemid = p.itemid);
Here you need to optimize your query, following is an example. You can use indexes for further optimization.
hope this helps