Hi, Im running the following query with a subselect in Derby + Hibernate, and its taking more than 20 seconds to complete when I pass 800 ids for the calculation. I dont have much experience with database optimization, so I wanted to know if this query could be faster, and how could I fix it.
Here´s the query: select sum(pesagem2_.peso) as col_0_0_ from ANIMAL animal0_ inner join PESAGEM_ANIMAL pesagens1_ on animal0_.ANIMAL_ID=pesagens1_.ANIMAL_ID inner join PESAGEM pesagem2_ on pesagens1_.PESAGEM_ID=pesagem2_.PESAGEM_ID where (animal0_.ANIMAL_ID in ( ... 800 ids ...)) and pesagem2_.DATA=(select max(pesagem4_.DATA) from PESAGEM_ANIMAL pesagens3_, PESAGEM pesagem4_ where animal0_.ANIMAL_ID=pesagens3_.ANIMAL_ID and pesagens3_.PESAGEM_ID=pesagem4_.PESAGEM_ID and pesagem4_.DATA< ? ) As far as I know, hibernate puts indexes on all primary and foreign keys, and I added an extra index in table PESAGEM(DATA) Thanks for your attention. -- Francisco Trindade [EMAIL PROTECTED] M3Tech - Tecnologia para o Agronegócio www.m3tech.com.br
