1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Report Performance issue.

Discussion in 'Oracle Forms and Reports' started by jagadekara, Dec 10, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I have report with the following query...

    SELECT a.invoice_amount
    ,a.exchange_rate
    ,a.tax
    ,a.gl_date
    ,(a.invoice_amount+a.tax) total_inv_amt
    ,a.exchange_rate*a.invoice_amount acct_inv_amt
    ,a.exchange_rate*a.tax acct_tax
    ,((a.exchange_rate*a.invoice_amount)+(a.exchange_rate*a.tax)) total_acct_inv_amt
    FROM (SELECT aia.invoice_amount
    ,aia.exchange_rate
    ,(sub query) tax
    ,(sub query) gl_date
    FROM ap_invoices_all aia
    WHERE 1=1
    AND aia.org_id=nvl:)P_ORG_ID,aia.org_id)
    ) a
    WHERE 1=1
    AND a.gl_date between :p_FROM_GL_DATE and :p_TO_GL_DATE
    ;

    this is sample query.
    So because of subquery and calculations report taking longtime to run. So if I use formula columns instead of subqueries and calculation, will it increase performance?

    Please give suggestions to increase performance.
     
  2. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi Jagadekara,

    I think formula columns can also be a factor for performance issue,

    hence can you try modifying the query to use joins instead of subqueries and see if it helps.

    Regards,
    Yowan Cristo
     
    jagadekara likes this.
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thank yowan,

    I will try to tune the query itself.
     
  4. reachrakesh007

    reachrakesh007 Active Member

    Messages:
    1
    Likes Received:
    1
    Trophy Points:
    85
    Location:
    Bhubaneswar
    You can make a package and do all the subquery calculations in it. Then you can call the procedures or functions in the query. That will definitely increase your performance.

    Regards,
    Rakesh
     
    jagadekara likes this.