OraFAQ Forum: Reports & Discoverer » Report take Long time

Home » Developer & Programmer » Reports & Discoverer » Report take Long time (Oracle 9i, Report 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Report take Long time [message #533712]

Thu, 01 December 2011 03:18

cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA

Senior Member

Hi,

I have Develop a Report for Transaction, it Contain
the Opending and the Closing Balance is arrive
through the transaction.

In it some parties doesn't have any transaction, in that
case our requirement is to display the Account details for
that Party.

I have develop one query and include in it. After include
that query the report take more time.

SELECT 
DISTINCT a.vendor_site_code,
        SYSDATE INVOICE_DATE ,
        a.accts_pay_code_combination_id ccid,
        b.segment1 ,
        b.vendor_name,
from    po_vendor_sites_all a, 
          po_vendors b, 
          ap_invoices_all c, 
          AP_INVOICE_DISTRIBUTIONS_ALL D 
where   a.vendor_id = b.vendor_id and
        a.vendor_id = c.vendor_id
        AND A.VENDOR_SITE_ID = C.VENDOR_SITE_ID
        AND C.INVOICE_ID = D.INVOICE_ID 
        AND D.ACCOUNTING_DATE NOT BETWEEN :p_from_date AND :p_to_date
        AND C.vendor_id = NVL(:p_vendor_id,c.vendor_id)
        and   A.VENDOR_SITE_ID not in (select VENDOR_SITE_ID 
                                                                from   ap_invoices_all A ,
                                                                         AP_INVOICE_DISTRIBUTIONS_ALL D
                       where  VENDOR_ID  =  NVL(:p_vendor_id,vendor_id) and
                    A.INVOICE_ID = D.INVOICE_ID AND
                                                                            to_date(accounting_date,'dd/mm/rrrr') between :p_from_date AND :p_to_date
                                                             )

Kindly Guidence me.

Regards,

C V S

Report message to a moderator

Re: Report take Long time [message #533734 is a reply to message #533712]

Thu, 01 December 2011 03:55

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If ACCOUNTING_DATE column is indexed, then this:

to_date(accounting_date,'dd/mm/rrrr')

might cause problems. What is this column's datatype? Is it a DATE? (Should be.) If it is, then TO_DATE a date is useless - you'd rather make sure that parameters' format is correct, i.e.

accounting_date between to_date(:p_from_date, 'dd/mm/rrrr') and to_date(:p_to_date, 'dd/mm/rrrr')

(of course, if these parameters are CHARACTERS. If they are DATES, no function at all:

accounting_date between :p_from_date and :p_to_date

Anyway: for much more information, Performance Tuning section is down the hall, second door on the left.

[Updated on: Thu, 01 December 2011 03:55]

Report message to a moderator

Previous Topic:	Oracle reports through Excel
Next Topic:	How to create the Half size report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 06:35:39 CST 2024