ObjectDB ObjectDB

Query execution time issue

#1

Hi,

I have a query similar to this one:

select 
person.name, 
person.surname, 
... 
...
from Entity e 
inner join e.person person 
inner join person.address address 
left join address.place addressPlace 
left join address.city addressCity 
left join address.county addressCounty 
left join person.birthPlace birthPlace 
left join person.birthCounty birthCounty 
left join person.citizenship citizenship 
left join e.codeList1 codeList1 
left join e.codeList2 codeList2 
left join e.codeList3 codeList3 
left join e.codeList4 codeList4 
left join e.codeList5 codeList5 

All of the codeLists can be null for an Entity, and there is only on Entity in the database.

Execution of this query (running with ObjectDB) takes about 10-12 seconds.
If i remove ANY 5 left joins, query execution time drops to about 50 milliseconds.
Adding a new left join on remaining 8 slows down query execution time drastically.

Please help me with this one,

Thanks!

edit
delete
#2

Queries with excessive JOINs are expected to be slow, but if you can post a simple minimal runnable test case (see posting instructions) that demonstrates the slow query, then we can check it.

ObjectDB Support
edit
delete
#3

Hi, 

I've attached a test db.

Running the following query in ObjectDB Explorer takes about 60+ seconds to execute (database is empty):

select e from TestEntity e 
left join e.cl1 cl1 
left join e.cl2 cl2 
left join e.cl3 cl3 
left join e.cl4 cl4 
left join e.cl5 cl5 
left join e.cl6 cl6 
left join e.cl7 cl7 
left join e.cl8 cl8 
left join e.cl9 cl9 
left join e.cl10 cl10 
left join e.cl11 cl11 
left join e.cl12 cl12 
left join e.cl13 cl13 
left join e.cl14 cl14 
left join e.cl15 cl15

If i reduce number of joins to, for exmple, 9 query execution time drops to about 60 milliseconds.

Kind regards,

Pero

 

edit
delete
#4

Thank you for this test case.

It demonstrates a slow query compilation (rather than a slow query execution).

Please try build 2.7.6_07 that should solve this issue.

ObjectDB Support
edit
delete
#5

Thank you,

Build 2.7.6_07 solves the issue.

edit
delete

Reply

To post on this website please sign in.