db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paulo Jesus <paulo.je...@mail.telepac.pt>
Subject join over more than 3 tables
Date Thu, 24 May 2007 16:11:06 GMT
I´m running pole position benchmark over derby 10.2.2 server mode.
In circuit Barcelona derby is extremely inefficient. Blame on lap "read"
that is running too many times with a inefficient query.

Looking to the querys from "read" and "query" (A and B)  i see a inner
join over 5 tables. The query plan is  confusing but i can see a table
scan over table barcelona3. It shouldn't be necessary, why is the
optimizer doing it?

Also,
I have indexes over columns id and barcelona2.b2
All tables have ~30500 elements.
All columns are integers, but B2 can be null.
Running query A and B 100 times, the execution of query B is always more
efficient than A. In order ~8seg to ~19seg. It´s a big difference, i
can´t explain it. Does anyone have some idea?

I will now start to try rewrite querys, but i find this two questions
interesting. And also find the perfect opportunity to start building the
tree of query plan.

PJ



query A
select * from barcelona0, barcelona1, barcelona2, barcelona3, barcelona4
where barcelona1.parent = barcelona0.id and barcelona2.parent =
barcelona1.id and barcelona3.parent = barcelona2.id and
barcelona4.parent = barcelona3.id and barcelona0.id=?


query B
select * from barcelona0, barcelona1, barcelona2, barcelona3, barcelona4
where barcelona1.parent = barcelona0.id and barcelona2.parent =
barcelona1.id and barcelona3.parent = barcelona2.id and
barcelona4.parent = barcelona3.id and barcelona2.b2=?

Mime
View raw message