db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dustin T. Clifford" <DCliff...@isdcorporation.com>
Subject Query Compilation Drastically Increased on Join
Date Wed, 18 Feb 2009 18:52:37 GMT
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


Mime
View raw message