2 Replies Latest reply on Mar 4, 2013 3:33 PM by Joel_C

    db LInk in report, Reflected  to speed.


      I have created classical report and in report i used dblinks in three columns.When i used dblink in report It created worst speed of my report.
      It take too long time to run the report.

      I created edit link on my report using code. procedure "Inv_in_apps" uses dblinks in code. How i can create edit links so that speed should not be reflected.The code below defined i used to create edit links.

      Am.Invoice_no as invoice_no,
      Case when Inv_in_apps(Am.Invoice_no) = 'N' then 
       '<a href="f?p=&APP_ID.:5:&SESSION.:MODIFY:&DEBUG.:5:P5_SBU_CODE,P5_INVOICE_NO:
      '||am.SBU_CODE||','||am.INVOICE_NO||':"><img src="#IMAGE_PREFIX#edit.gif" alt="Edit"></a>'
      when Inv_in_apps(Am.Invoice_no) = 'Y' then
      '<img src="#IMAGE_PREFIX#edit.gif" alt="Invoice Transfered">'
      END AS the_Edit,
      Case when Inv_in_apps(Am.Invoice_no) = 'Y' THEN
      '<font color ="Gray"></b>Revenue</b></font>'
      '<a href="f?p=&APP_ID.:41:&SESSION.:MODIFY:&DEBUG.:41:P41_sbu_code,P41_Invoice_no:'
      <font color =GRAY><b>Revenue</b></font></a>'
      END AS Revenue
      from "AR_INVOICE_MAS" am
      Thanks & Regards
        • 1. Re: db LInk in report, Reflected  to speed.

          Have you tried running this outside of APEX? I would guess that you would still have the same problem.

          This is really a tuning request that might be best addressed in the SQL & PL/SQL forum (there is a FAQ at the top of the forum that will tell you how best to post tuning requests.

          Here's a quick idea though, look up the DRIVING_SITE hint.

          My guess is that 'Inv_in_apps' is actually a function and not a procedure. Depending on your data, you may benefit from results caching.

          Can't really help much more without seeing an explain plan etc.


          1 person found this helpful
          • 2. Re: db LInk in report, Reflected  to speed.
            I'd agree whole-heartedly with Ben here - running queries over dblinks can be a bit of a minefield TBH and best avoided if at all possible.

            One thing: If the data you are querying is relatively static (changes infrequently) or is updated on regular schedule (e.g. once per day, hour etc.) and is generally read-only in nature, it might be worth exploring the use of Materialized views which allows you to replicate a data-set locally, thus effectively eliminating the need to query across databases - their use in this context is beyond the scope of the APEX forum, however.
            1 person found this helpful