db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Witold Szczerba <pljosh.m...@gmail.com>
Subject Query execution never ends
Date Fri, 19 Mar 2010 13:19:28 GMT
Hi there,
I have problem with query execution - it never ends (or it takes so
long that I have never been able to wait that long).
There are many many tables in database, so I will introduce only the
one which are participating in my nightmare.

Tables:
APPUSER(ID, NAME, others...)
BROKERCOMPANY (ID, NAME, others...)
BROKERCOMPANYBRANCH (ID, BROKERCOMPANY_ID, NAME, others...)

BROKER_USER (BROKER_ID, USER_ID)
BROKER_BRANCH (BROKER_ID, BRANCH_ID)

CONTRACT (ID, INSURED_ID, BROKERCOMPANY_ID, others...)
CONTRACTSUBJECT(ID, CONTRACT_ID, others)

and one view:
CREATE VIEW user_contract AS
 SELECT
    bu.user_id,
    con.id contract_id
 FROM BROKER_USER bu
 JOIN BROKER_BRANCH bb ON bu.BROKER_ID=bb.BROKER_ID
 JOIN BROKERCOMPANYBRANCH bcb ON bb.BRANCH_ID=bcb.ID
 JOIN Contract co ON bcb.brokerCompany_id=con.brokerCompany_id;

This view joins application users with contracts, as you see - the
connection is not that simple because, contracts are assigned to
broker companies and users are assigned to brokers and brokers are
assigned to branches...

Now, there is my first test query like this:
-----
SELECT count(*)
FROM contract co
JOIN user_contract uc ON co.id=uc.contract_id
-----
On my PC at work it takes 1 or 2 seconds to complete.

And now, second query like this:
-----
SELECT count(*)
FROM contract co
LEFT JOIN ContractSubject insured ON insured.contract_id=co.id AND
insured.id=co.insured_id
-----
This query takes < 1 second.

The problem begins when I want to combine both queries like this:
-----
SELECT count(*)
FROM contract co
JOIN user_contract uc ON co.id=uc.contract_id
LEFT JOIN ContractSubject insured ON insured.contract_id=co.id AND
insured.id=co.insured_id
-----
The query above never ends, however, when I change "LEFT JOIN" to
"JOIN" in last line - it executes in 2-3 seconds.

I was trying to read execution plans of those queries which end, but I
really cannot see anything strange in those plans, but I must admit I
am not sure I understand them :/ I would like to see the plan for the
never ending query, but unfortunately - Derby will not display plan
until query ends. I will try it at home (much faster PC) during this
weekend. I was also trying to reproduce this on empty database, but
with no data the query executes in lightning speed (I would loose my
head/end in prison if I would share this database).

My question is: do you have ANY idea what can be wrong with it?

Regards,
Witold Szczerba

Mime
View raw message