All,
We current offer derby, among others, as a supported DBMS in our offering. We
have recently modified a query by adding a couple of tables to a simple join.
We have noticed a drastic increase in query compilation time on large data
sets. The execution time (post compile/optimization).
In our investigation of this issue we have seen that specifying joinOrder=FIXED
reduces the compilation time back to normal but we think it odd that
compilation should take 12min. longer without this option (even thought the
optimizer reports the same estimated cost). Does anybody have any suggestions
or is this a know bug?
I have include the abridged logs, for brevity, below from both runs (with and
without joinOrder). Please, let me know if you need any further information or
the complete logs, please, let me know.
Without joiOrder
2009-02-18 14:47:05.080 GMT Thread[main,5,main] (XID = 2564601), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), Begin compiling prepared
statement: select settlement_transaction_id from
settlement_transaction, journal_credit,
bank_deposit_batch, bank_run, bank, card_type, corporation,
company, subsidiary, location
where settlement_transaction.bank_deposit_batch_id =
bank_deposit_batch.bank_deposit_batch_id
and settlement_transaction.bank_id = bank.bank_id and
settlement_transaction.journal_id = journal_credit.journalId and
bank_deposit_batch.bank_run_id = bank_run.bank_run_id and
journal_credit.cardTypeId = card_type.cardTypeId
and journal_credit.corporationId = corporation.corporationId
and journal_credit.companyId = company.companyId and
journal_credit.subsidiaryId = subsidiary.subsidiaryId
and journal_credit.locationId = location.locationId and
settlement_transaction.business_date >= '2009-02-16' and
settlement_transaction.business_date <= '2009-02-16'
order by business_date, corporation.name, company.name,
subsidiary.name, location.externallocationId, location.name, card_type.name
:End prepared statement
2009-02-18 14:59:08.212 GMT Thread[main,5,main] (XID = 2564601), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), End compiling prepared
statement: select settlement_transaction_id from
settlement_transaction, journal_credit,
...
2009-02-18 14:59:08.212 GMT Thread[main,5,main] (XID = 2564601), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), End compiling prepared
statement: select settlement_transaction_id from
settlement_transaction, journal_credit,
bank_deposit_batch, bank_run, bank, card_type, corporation,
company, subsidiary, location
G
where settlement_transaction.bank_deposit_batch_id =
bank_deposit_batch.bank_deposit_batch_id
and settlement_transaction.bank_id = bank.bank_id and
settlement_transaction.journal_id = journal_credit.journalId and
bank_deposit_batch.bank_run_id = bank_run.bank_run_id and
journal_credit.cardTypeId = card_type.cardTypeId
and journal_credit.corporationId = corporation.corporatiGnId
and journal_credit.companyId = company.companyId and
journal_credit.subsidiaryId = subsidiary.subsidiaryId
and journal_credit.locationId = location.locationId and
settlement_transaction.business_date >= '2009-02-16' and
settlement_transaction.business_date <= '2009-02-16'
order by business_date, corporation.name, company.name,
subsidiary.name, location.externallocationId, location.name, card_type.name
:End prepared statement
...
2009-02-18 14:59:08.258 GMT Thread[main,5,main] (XID = 2564601), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), Executing prepared statement:
select settlement_transaction_id from
settlement_transaction, journal_credit,
bank_deposit_batch, bank_run, bank, card_type, corporation,
company, subsidiary, location
where settlement_transaction.bank_deposit_batch_id =
bank_deposit_batch.bank_deposit_batch_id
and settlement_transaction.bank_id = bank.bank_id and
settlement_transaction.journal_id = journal_credit.journalId and
bank_deposit_batch.bank_run_id = bank_run.bank_run_id and
journal_credit.cardTypeId = card_type.cardTypeId
and journal_credit.corporationId = corporation.corporationId
and journal_credit.companyId = company.companyId and
journal_credit.subsidiaryId = subsidiary.subsidiaryId
and journal_credit.locationId = location.locationId and
settlement_transaction.business_date >= '2009-02-16' and
settlement_transaction.business_date <= '2009-02-16'
order by business_date, corporation.name, company.name,
subsidiary.name, location.externallocationId, location.name, card_type.name
:End prepared statement
...
2009-02-18 14:59:08.321 GMT Thread[main,5,main] (XID = 2564601), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), Committing
With joinOrder
2009-02-18 15:02:26.006 GMT Thread[main,5,main] (XID = 2564609), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), Begin compiling prepared
statement: select settlement_transaction_id from --DERBY-PROPERTIES
joinOrder=FIXED
settlement_transaction, journal_credit,
bank_deposit_batch, bank_run, bank, card_type, corporation,
company, subsidiary, location
where settlement_transaction.bank_deposit_batch_id =
bank_deposit_batch.bank_deposit_batch_id
and settlement_transaction.bank_id = bank.bank_id and
settlement_transaction.journal_id = journal_credit.journalId and
bank_deposit_batch.bank_run_id = bank_run.bank_run_id and
journal_credit.cardTypeId = card_type.cardTypeId
and journal_credit.corporationId = corporation.corporationId
and journal_credit.companyId = company.companyId and
journal_credit.subsidiaryId = subsidiary.subsidiaryId
and journal_credit.locationId = location.locationId and
settlement_transaction.business_date >= '2009-02-16' and
settlement_transaction.business_date <= '2009-02-16'
order by business_date, corporation.name, company.name,
subsidiary.name, location.externallocationId, location.name, card_type.name
:End prepared statement
...
2009-02-18 15:02:26.272 GMT Thread[main,5,main] (XID = 2564609), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), End compiling prepared
statement: select settlement_transaction_id from --DERBY-PROPERTIES
joinOrder=FIXED
settlement_transaction, journal_credit,
bank_deposit_batch, bank_run, bank, card_type, corporation,
company, subsidiary, location
...
2009-02-18 15:02:26.318 GMT Thread[main,5,main] (XID = 2564609), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), Executing prepared statement:
select settlement_transaction_id from --DERBY-PROPERTIES joinOrder=FIXED
settlement_transaction, journal_credit,
bank_deposit_batch, bank_run, bank, card_type, corporation,
company, subsidiary, location
where settlement_transaction.bank_deposit_batch_id =
bank_deposit_batch.bank_deposit_batch_id
and settlement_transaction.bank_id = bank.bank_id and
settlement_transaction.journal_id = journal_credit.journalId and
bank_deposit_batch.bank_run_id = bank_run.bank_run_id and
journal_credit.cardTypeId = card_type.cardTypeId
and journal_credit.corporationId = corporation.corporationId
and journal_credit.companyId = company.companyId and
journal_credit.subsidiaryId = subsidiary.subsidiaryId
and journal_credit.locationId = location.locationId and
settlement_transaction.business_date >= '2009-02-16' and
settlement_transaction.business_date <= '2009-02-16'
order by business_date, corporation.name, company.name,
subsidiary.name, location.externallocationId, location.name, card_type.name
:End prepared statement
...
2009-02-18 15:02:26.381 GMT Thread[main,5,main] (XID = 2564609), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), Committing
2009-02-18 15:02:29.443 GMT Thread[main,5,main] (XID = 2564609), (SESSIONID =
0), (DATABASE = ./db/report1), (DRDAID = null), Rolling back
Dustin Clifford
System Engineer
