db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Francisco Trindade <franci...@m3tech.com.br>
Subject Subselect Performance Problem
Date Mon, 25 Jun 2007 21:45:04 GMT

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

M3Tech - Tecnologia para o Agroneg├│cio

View raw message