Archives of the TeradataForum

Message Posted: Fri, 11 Feb 2005 @ 20:39:42 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Query Length Time Increase

From:		Dieter Noeth

Craig Ferry wrote:

I added the additional multi-column statistics recommended by helpstats and I am still encountering the same problem. For some reason with the additional where clause, it is running a lot longer.

IMHO the "And b.region_id = 8120" condition already exists within the subquery, but the optimizer doesn't recognize that.

Some additional remarks inline:

     Where a.date_paid is Null
     And a.vendor_no In
       (
        Select vendor_no
        From dss_tables.ap_open_paid_dly
        Where vendor_no Not In ('PHLPURGE', 'PGHPURGE', 'CHGPURGE','SFXPURGE',
        'HDQPURGE', 'TORPURGE', 'PHL00001', 'PGH00001',
        'CHG00001', 'SFX00001', 'HDQ00001', 'TOR00001', 'PHL00002','PGH00002',
        'CHG00002', 'SFX00002', 'HDQ00002', 'TOR00002',
        'PHL00003', 'PGH00003', 'CHG00003', 'SFX00003', 'HDQ00003','TOR00003',
        'PHL00004', 'PGH00004', 'TOR20002')
        And date_paid is Null
        And date_invoice < '2004-11-12'
        And date_invoice >= '2003-01-01'
        And branch_id Not In (7147,7528, 7529)
     ****** duplicate condition 1

        And a.branch_id In
     ******^

That "a." makes it a correlated subquery, this is probably unintended!!!

         (Select branch_id
          From branch
          Where REGION_ID = 8120)
     ****** duplicate condition 2

        Group By vendor_no
        Having Sum(net_amount)<0
       )
     And a.branch_id Not In (7147, 7528, 7529)
     ****** duplicate condition 1

     And a.net_amount < 0
     And a.date_invoice < '2004-11-12'
     And a.date_invoice >= '2003-01-01'
     And b.region_id = 8120
     ****** duplicate condition 2

Removing the correlated subquery should help, maybe removing the duplicate conditions also:

     Where a.date_paid is Null
     And a.vendor_no In
       (
        Select vendor_no
        From dss_tables.ap_open_paid_dly
        Where vendor_no Not In ('PHLPURGE', 'PGHPURGE', 'CHGPURGE','SFXPURGE',
        'HDQPURGE', 'TORPURGE', 'PHL00001', 'PGH00001',
        'CHG00001', 'SFX00001', 'HDQ00001', 'TOR00001', 'PHL00002','PGH00002',
        'CHG00002', 'SFX00002', 'HDQ00002', 'TOR00002',
        'PHL00003', 'PGH00003', 'CHG00003', 'SFX00003', 'HDQ00003','TOR00003',
        'PHL00004', 'PGH00004', 'TOR20002')
        And date_paid is Null
        And date_invoice < '2004-11-12'
        And date_invoice >= '2003-01-01'
        And branch_id In
          (Select branch_id
           From branch
           Where REGION_ID = 8120
           And branch_id Not In (7147, 7528, 7529)
          )
        Group By vendor_no
        Having Sum(net_amount)<0
       )
     And a.net_amount < 0
     And a.date_invoice < '2004-11-12'
     And a.date_invoice >= '2003-01-01'

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference