Michael, I agree with you that it is not 'simple' and we do have plans to optimize this query amongst others.
The question still remains to me though, why after adding 1 or 2 tables (the query had 8 tables joined before) does the compile time jump so drastically when the execution time remains, approximately, the same. To add a little more to the mix, this query has shown little or no time increases on Oracle and SQL Server. Dustin From: Michael Segel [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, February 18, 2009 1:58 PM To: 'Derby Discussion' Subject: RE: Query Compilation Drastically Increased on Join 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:[email protected]] Sent: Wednesday, February 18, 2009 12:53 PM To: [email protected] 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
