Home » Developer & Programmer » Reports & Discoverer » Report take Long time (Oracle 9i, Report 6i)
Report take Long time [message #533712] Thu, 01 December 2011 03:18 Go to next message
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
Re: Report take Long time [message #533734 is a reply to message #533712] Thu, 01 December 2011 03:55 Go to previous message
Littlefoot
Messages: 21806
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:
  


Current Time: Fri Mar 29 05:59:02 CDT 2024