db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Query Compilation Drastically Increased on Join
Date Wed, 18 Feb 2009 18:58:15 GMT
You're attempting to join 10 tables.

 

That's going to hurt performance. 10 tables is no longer a 'simple' join.

 

 

  _____  

From: Dustin T. Clifford [mailto:DClifford@isdcorporation.com] 
Sent: Wednesday, February 18, 2009 12:53 PM
To: derby-user@db.apache.org
Subject: Query Compilation Drastically Increased on Join

 

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