Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 88080 invoked from network); 18 Feb 2009 18:56:04 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 18 Feb 2009 18:56:04 -0000 Received: (qmail 6562 invoked by uid 500); 18 Feb 2009 18:56:03 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 6293 invoked by uid 500); 18 Feb 2009 18:56:02 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 6279 invoked by uid 99); 18 Feb 2009 18:56:01 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 18 Feb 2009 10:56:01 -0800 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [69.214.17.69] (HELO filter.isdcorporation.com) (69.214.17.69) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 18 Feb 2009 18:55:52 +0000 Received: from athena.isdwhq.com ([::1]) by athena.isdwhq.com ([::1]) with mapi; Wed, 18 Feb 2009 13:52:39 -0500 From: "Dustin T. Clifford" To: "derby-user@db.apache.org" Date: Wed, 18 Feb 2009 13:52:37 -0500 Subject: Query Compilation Drastically Increased on Join Thread-Topic: Query Compilation Drastically Increased on Join Thread-Index: AcmR+hGR03TUkdQFTHSDyYkBZBfU4Q== Message-ID: <64A84ACFB244464D8C7086F909034D540DB3D80C@athena.isdwhq.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_64A84ACFB244464D8C7086F909034D540DB3D80Cathenaisdwhqcom_" MIME-Version: 1.0 X-Virus-Checked: Checked by ClamAV on apache.org --_000_64A84ACFB244464D8C7086F909034D540DB3D80Cathenaisdwhqcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 larg= e data sets. The execution time (post compile/optimization). In our investigation of this issue we have seen that specifying joinOrder= =3DFIXED reduces the compilation time back to normal but we think it odd th= at compilation should take 12min. longer without this option (even thought = the optimizer reports the same estimated cost). Does anybody have any sugge= stions or is this a know bug? I have include the abridged logs, for brevity, below from both runs (with a= nd without joinOrder). Please, let me know if you need any further informa= tion or the complete logs, please, let me know. Without joiOrder 2009-02-18 14:47:05.080 GMT Thread[main,5,main] (XID =3D 2564601), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D 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 =3D bank_deposit_batch= .bank_deposit_batch_id and settlement_transaction.bank_id =3D bank.bank_id and settle= ment_transaction.journal_id =3D journal_credit.journalId and bank_deposit_batch.bank_run_id =3D bank_run.bank_run_id and journa= l_credit.cardTypeId =3D card_type.cardTypeId and journal_credit.corporationId =3D corporation.corporatio= nId and journal_credit.companyId =3D company.companyId and = journal_credit.subsidiaryId =3D subsidiary.subsidiaryId and journal_credit.locationId =3D location.locationId = and settlement_transaction.business_date >=3D '2009-02-16' and settleme= nt_transaction.business_date <=3D '2009-02-16' order by business_date, corporation.name, company.name, su= bsidiary.name, location.externallocationId, location.name, card_type.name = :End prepared statement 2009-02-18 14:59:08.212 GMT Thread[main,5,main] (XID =3D 2564601), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), End compiling pr= epared statement: select settlement_transaction_id from settlement_transaction, journal_credit, ... 2009-02-18 14:59:08.212 GMT Thread[main,5,main] (XID =3D 2564601), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), End compiling pr= epared 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 =3D bank_deposit_batch= .bank_deposit_batch_id and settlement_transaction.bank_id =3D bank.bank_id and settle= ment_transaction.journal_id =3D journal_credit.journalId and bank_deposit_batch.bank_run_id =3D bank_run.bank_run_id and journa= l_credit.cardTypeId =3D card_type.cardTypeId and journal_credit.corporationId =3D corporation.corporatiG= nId and journal_credit.companyId =3D company.companyId and = journal_credit.subsidiaryId =3D subsidiary.subsidiaryId and journal_credit.locationId =3D location.locationId = and settlement_transaction.business_date >=3D '2009-02-16' and settleme= nt_transaction.business_date <=3D '2009-02-16' order by business_date, corporation.name, company.name, su= bsidiary.name, location.externallocationId, location.name, card_type.name = :End prepared statement ... 2009-02-18 14:59:08.258 GMT Thread[main,5,main] (XID =3D 2564601), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Executing prepar= ed 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 =3D bank_deposit_batch= .bank_deposit_batch_id and settlement_transaction.bank_id =3D bank.bank_id and settle= ment_transaction.journal_id =3D journal_credit.journalId and bank_deposit_batch.bank_run_id =3D bank_run.bank_run_id and journa= l_credit.cardTypeId =3D card_type.cardTypeId and journal_credit.corporationId =3D corporation.corporatio= nId and journal_credit.companyId =3D company.companyId and = journal_credit.subsidiaryId =3D subsidiary.subsidiaryId and journal_credit.locationId =3D location.locationId = and settlement_transaction.business_date >=3D '2009-02-16' and settleme= nt_transaction.business_date <=3D '2009-02-16' order by business_date, corporation.name, company.name, su= bsidiary.name, location.externallocationId, location.name, card_type.name = :End prepared statement ... 2009-02-18 14:59:08.321 GMT Thread[main,5,main] (XID =3D 2564601), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Committing With joinOrder 2009-02-18 15:02:26.006 GMT Thread[main,5,main] (XID =3D 2564609), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Begin compiling = prepared statement: select settlement_transaction_id from --DERBY-PR= OPERTIES joinOrder=3DFIXED settlement_transaction, journal_credit, bank_deposit_batch, bank_run, bank, card_type, corporation, company, subsidiary, location where settlement_transaction.bank_deposit_batch_id =3D bank_deposit_batch= .bank_deposit_batch_id and settlement_transaction.bank_id =3D bank.bank_id and settle= ment_transaction.journal_id =3D journal_credit.journalId and bank_deposit_batch.bank_run_id =3D bank_run.bank_run_id and journa= l_credit.cardTypeId =3D card_type.cardTypeId and journal_credit.corporationId =3D corporation.corporatio= nId and journal_credit.companyId =3D company.companyId and = journal_credit.subsidiaryId =3D subsidiary.subsidiaryId and journal_credit.locationId =3D location.locationId = and settlement_transaction.business_date >=3D '2009-02-16' and settleme= nt_transaction.business_date <=3D '2009-02-16' order by business_date, corporation.name, company.name, su= bsidiary.name, location.externallocationId, location.name, card_type.name = :End prepared statement ... 2009-02-18 15:02:26.272 GMT Thread[main,5,main] (XID =3D 2564609), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), End compiling pr= epared statement: select settlement_transaction_id from --DERBY-PROP= ERTIES joinOrder=3DFIXED 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 =3D 2564609), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Executing prepar= ed statement: select settlement_transaction_id from --DERBY-PROPERTI= ES joinOrder=3DFIXED settlement_transaction, journal_credit, bank_deposit_batch, bank_run, bank, card_type, corporation, company, subsidiary, location where settlement_transaction.bank_deposit_batch_id =3D bank_deposit_batch= .bank_deposit_batch_id and settlement_transaction.bank_id =3D bank.bank_id and settle= ment_transaction.journal_id =3D journal_credit.journalId and bank_deposit_batch.bank_run_id =3D bank_run.bank_run_id and journa= l_credit.cardTypeId =3D card_type.cardTypeId and journal_credit.corporationId =3D corporation.corporatio= nId and journal_credit.companyId =3D company.companyId and = journal_credit.subsidiaryId =3D subsidiary.subsidiaryId and journal_credit.locationId =3D location.locationId = and settlement_transaction.business_date >=3D '2009-02-16' and settleme= nt_transaction.business_date <=3D '2009-02-16' order by business_date, corporation.name, company.name, su= bsidiary.name, location.externallocationId, location.name, card_type.name = :End prepared statement ... 2009-02-18 15:02:26.381 GMT Thread[main,5,main] (XID =3D 2564609), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Committing 2009-02-18 15:02:29.443 GMT Thread[main,5,main] (XID =3D 2564609), (SESSION= ID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Rolling back Dustin Clifford System Engineer --_000_64A84ACFB244464D8C7086F909034D540DB3D80Cathenaisdwhqcom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

All,

 

We current offer derby, among others, as a supported D= BMS 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 compil= ation time on large data sets. The execution time (post compile/optimization).

 

In our investigation of this issue we have seen that specifying joinOrder=3DFIXED reduces the compilation time back to normal bu= t 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 a= ny suggestions or is this a know bug?

 

I have include the abridged logs, for brevity, below f= rom both runs (with and without joinOrder).  Please, let me know if you ne= ed any further information or the complete logs, please, let me know.

 

Without joiOrder<= /b>

 

2009-02-18 14:47:05.080 GMT Thread[main,5,main] (XID =3D 2564601), (SESSIONID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Begin compiling prepared statement: selec= t settlement_transaction_id        from

settlement_transaction, journal_credit,   

bank_deposit_batch, &n= bsp;   bank_run,     bank,     card_type,     corporation,    

company,   &= nbsp; subsidiary,     location

  where settlement_transaction.bank_deposit_batch_id =3D bank_deposit_batch.bank_deposit_batch_id

     and     settlement_transaction.bank_id =3D bank.bank_id and     settlement_transaction.journal_id =3D journal_credit.journalId     and

 bank_deposit_batch.ba= nk_run_id =3D bank_run.bank_run_id     and    = ; journal_credit.cardTypeId =3D card_type.cardTypeId     =

and    =              journal_credit.corporationId =3D corporation.corporationId        &n= bsp;       

and    =     journal_credit.companyId =3D company.companyId        and        journal_credit.subsidiaryId = =3D subsidiary.subsidiaryId

    &nb= sp;   and        journal_credit.locationId =3D location.locationId         and      

settlement_transaction.busi= ness_date >=3D '2009-02-16'   and       settlement_transaction.business_date <=3D '2009-02-16'

    &nb= sp;            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 =3D 2564601), (SESSIONID =3D 0), (DATABASE =3D ./d= b/report1), (DRDAID =3D null), End compiling prepared statement: select settlement_transaction_id        from

settlement_transaction, journal_credit,   

    &nb= sp;           …

2009-02-18 14:59:08.212 GMT Thread[main,5,main] (XID =3D 2564601), (SESSIONID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), End compiling prepared statement: select settlement_transaction_id        from

settlement_transaction, journal_credit,   

bank_deposit_batch, &n= bsp;   bank_run,     bank,     card_type,     corporation,    

company,   &= nbsp; subsidiary,     location

G

  where settlement_transaction.bank_deposit_batch_id =3D bank_deposit_batch.bank_deposit_batch_id

     and     settlement_transaction.bank_id =3D bank.bank_id and     settlement_transaction.journal_id =3D journal_credit.journalId     and

 bank_deposit_batch.ba= nk_run_id =3D bank_run.bank_run_id     and    = ; journal_credit.cardTypeId =3D card_type.cardTypeId     =

and    =              journal_credit.corporationId =3D corporation.corporatiGnId   = ;            &n= bsp;

and    =     journal_credit.companyId =3D company.companyId        and        journal_credit.subsidiaryId = =3D subsidiary.subsidiaryId

    &nb= sp;   and        journal_credit.locationId =3D location.locationId         and      

settlement_transaction.busi= ness_date >=3D '2009-02-16'   and       settlement_transaction.business_date <=3D '2009-02-16'

    &nb= sp;            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 =3D 2564601), (SESSIONID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Executing prepared statement: select settlement_transaction_id        from

settlement_transaction, jou= rnal_credit,   

bank_deposit_batch, &n= bsp;   bank_run,     bank,     card_type,     corporation,    

company,   &= nbsp; subsidiary,     location

  where settlement_transaction.bank_deposit_batch_id =3D bank_deposit_batch.bank_deposit_batch_id

     and     settlement_transaction.bank_id =3D bank.bank_id and     settlement_transaction.journal_id =3D journal_credit.journalId     and

 bank_deposit_batch.ba= nk_run_id =3D bank_run.bank_run_id     and    = ; journal_credit.cardTypeId =3D card_type.cardTypeId     =

and    =              journal_credit.corporationId =3D corporation.corporationId        &n= bsp;       

and    =     journal_credit.companyId =3D company.companyId        and        journal_credit.subsidiaryId = =3D subsidiary.subsidiaryId

    &nb= sp;   and        journal_credit.locationId =3D= location.locationId         and      

settlement_transaction.busi= ness_date >=3D '2009-02-16'   and       settlement_transaction.business_date <=3D '2009-02-16'

    &nb= sp;            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 =3D 2564601), (SESSIONID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Committing

With joinOrder

 

2009-02-18 15:02:26.006 GMT Thread[main,5,main] (XID =3D 2564609), (SESSIONID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Begin compiling prepared statement: selec= t settlement_transaction_id        from --DERBY-PROPERTIES joinOrder=3DFIXED

settlement_transaction, journal_credit,   

bank_deposit_batch, &n= bsp;   bank_run,     bank,     card_type,     corporation,    

company,   &= nbsp; subsidiary,     location

  where settlement_transaction.bank_deposit_batch_id =3D bank_deposit_batch.bank_deposit_batch_id

     and     settlement_transaction.bank_id =3D bank.bank_id and     settlement_transaction.journal_id =3D journal_credit.journalId     and

 bank_deposit_batch.ba= nk_run_id =3D bank_run.bank_run_id     and    = ; journal_credit.cardTypeId =3D card_type.cardTypeId     =

and    =              journal_credit.corporationId =3D corporation.corporationId        &n= bsp;       

and    =     journal_credit.companyId =3D company.companyId        and        journal_credit.subsidiaryId = =3D subsidiary.subsidiaryId

    &nb= sp;   and        journal_credit.locationId =3D location.locationId         and      

settlement_transaction.busi= ness_date >=3D '2009-02-16'   and       settlement_transaction.business_date <=3D '2009-02-16'

    &nb= sp;            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 =3D 2564609), (SESSIONID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), End compiling prepared statement: select settlement_transaction_id        from --DERBY-PROPERTIES joinOrder=3DFIXED

settlement_transaction, journal_credit,   

bank_deposit_batch, &n= bsp;   bank_run,     bank,     card_type,     corporation,    

company,   &= nbsp; subsidiary,     location

…

 

2009-02-18 15:02:26.318 GMT Thread[main,5,main] (XID =3D 2564609), (SESSIONID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Executing prepared statement: select settlement_transaction_id        from --DERBY-PROPERTIES joinOrder=3DFIXED

settlement_transaction, journal_credit,   

bank_deposit_batch, &n= bsp;   bank_run,     bank,     card_type,     corporation,    

company,   &= nbsp; subsidiary,     location

  where settlement_transaction.bank_deposit_batch_id =3D bank_deposit_batch.bank_deposit_batch_id

     and     settlement_transaction.bank_id =3D bank.bank_id and     settlement_transaction.journal_id =3D journal_credit.journalId     and

 bank_deposit_batch.ba= nk_run_id =3D bank_run.bank_run_id     and    = ; journal_credit.cardTypeId =3D card_type.cardTypeId     =

and    =              journal_credit.corporationId =3D corporation.corporationId        &n= bsp;       

and    =     journal_credit.companyId =3D company.companyId        and        journal_credit.subsidiaryId = =3D subsidiary.subsidiaryId

    &nb= sp;   and        journal_credit.locationId =3D location.locationId         and      

settlement_transaction.busi= ness_date >=3D '2009-02-16'   and       settlement_transaction.business_date <=3D '2009-02-16'

    &nb= sp;            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 =3D 2564609), (SESSIONID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Committing

2009-02-18 15:02:29.443 GMT Thread[main,5,main] (XID =3D 2564609), (SESSIONID =3D 0), (DATABASE =3D ./db/report1), (DRDAID =3D null), Rolling back

 

 

Dustin Clifford

System Engineer 

 

--_000_64A84ACFB244464D8C7086F909034D540DB3D80Cathenaisdwhqcom_--