This discussion is archived
13 Replies Latest reply: Sep 11, 2013 1:44 AM by Gus C RSS

Interactive Report Performance With Conditional Link

Gus C Journeyer
Currently Being Moderated

Apex 3.2

 

I have a interactive report.

The underlying sql would return 127000 rows

The sql is

select

  lde.ods_system,

  lde.ldekey,

  msg.sendersystem, 

  msg.messagetype,

  msg.messageversion,

  msg.msgseqnumber,

  msg.alternatekey,

  msg.crudmarker,

  msg.clrbookdate,

  msg.clrbookresult,

  lower('udf_'||msg.messagetype) button,

  lde.ldekey||'.'||msg.alternatekey||'.'||msg.msgseqnumber udm_key

from

  clr_esbmessageheader msg,

  clr_adm_systemmessage adm,

  udm_lde lde

where

  adm.ldeid = lde.ldeid and

  msg.sendersystem = adm.system and

  msg.messagetype = adm.messagetype and

  msg.messageversion = adm.messageversion and

  msg.receiversystem = 'SCIPS'

order by msg.clrbookdate desc

 

This report only takes 1 second to display.

I need to add a conditional link to another page, so I used

 

case

when lower('udf_'||msg.messagetype) = 'udf_distreceipt' then

'<a class="type" href="' || apex_util.prepare_url('f?p='||:APP_ID||':52:'||:APP_SESSION||'::'||:DEBUG||':RIR'||':IR_MSG_KEY,P52_PG:'|| lde.ldekey||'.'|| msg.alternatekey ||'.'|| msg.msgseqnumber ||','|| 50, null, 'SESSION') || '"title="Go to udf_distreceipt Report">udf_distreceipt</a>'

else 'no link' end table_link

 

The sql seems to be ok, because the report accepted it, but selecting the new column and saving the report takes forever (over 2 mins)

 

Now the report takes over 2 minutes to run and I still need to add more conditions.

Have I coded the link incorrectly ?

 

Gus

  • 1. Re: Interactive Report Performance With Conditional Link
    Gus C Journeyer
    Currently Being Moderated

    Additional information.

    The sql with the 3 conditional links takes 21 seconds in toad

     

    Gus

  • 2. Re: Interactive Report Performance With Conditional Link
    Gus C Journeyer
    Currently Being Moderated

    At the moment I have created 3 standard column links.

    The report performs normally, but I would rather have the conditional links

     

    Any ideas appreciated

     

    Gus

  • 3. Re: Interactive Report Performance With Conditional Link
    Tom Petrus Expert
    Currently Being Moderated

    Can you try to produce the link without the call to apex_util.prepare_url and see how that performs?

  • 4. Re: Interactive Report Performance With Conditional Link
    Gus C Journeyer
    Currently Being Moderated

    Having one of those days.

    For the life of me I cannot get the link code correct

     

    case

    when lower('udf_'||msg.messagetype) = 'udf_distreceipt' then

    '<a class="type" href="' || ('f?p='||:APP_ID||':52:'||:APP_SESSION||'::'||:DEBUG||':RIR'||':IR_MSG_KEY,P52_PG:'|| lde.ldekey||'.'|| msg.alternatekey ||'.'|| msg.msgseqnumber ||','|| 50, null) || '">udf_distreceipt</a>'

    else null END table_link

  • 5. Re: Interactive Report Performance With Conditional Link
    Tom Petrus Expert
    Currently Being Moderated

    case

    when lower('udf_'||msg.messagetype) = 'udf_distreceipt' then

    '<a class="type" href="' || 'f?p='||:APP_ID||':52:'||:APP_SESSION||'::'||:DEBUG||':RIR'||':IR_MSG_KEY,P52_PG:'|| lde.ldekey||'.'|| msg.alternatekey ||'.'|| msg.msgseqnumber ||','|| 50 || '">udf_distreceipt</a>'

    else null END table_link

     

    You had the brackets around the url and still ", null" in there from the prepare_url call (the charset parameter)

  • 6. Re: Interactive Report Performance With Conditional Link
    Paul Broughton Pro
    Currently Being Moderated

    Hi Gus,

     

    Are you wanting to put the link in the query for a specific reason?

     

    I had to do a similar thing in the past and just completed the column link section for the column.

     

    Why not just have the following in the query:

     

    case

    when lower('udf_'||msg.messagetype) = 'udf_distreceipt' then

    udf_distreceipt

    else null END table_link

     

    Then do the linking using column link section:

     

    You would specify your link text as #TABLE_LINK# which should then be conditionally displayed due to the case statement, then add in all the page item and values to pass across using a normal link column.


    Thanks


    Paul

  • 7. Re: Interactive Report Performance With Conditional Link
    Gus C Journeyer
    Currently Being Moderated

    I need 3 conditions, ie the link could go to 3 diffeent pages depending on the value of lower('udf_'||msg.messagetype)

     

    Gus

  • 8. Re: Interactive Report Performance With Conditional Link
    Gus C Journeyer
    Currently Being Moderated

    Thanks for that.

    The report now runs with an acceptable speed.

     

    The problem now is that a checksum is required on the page being called, which is why I used apex_util.prepare_url

     

    Gus

  • 9. Re: Interactive Report Performance With Conditional Link
    Gus C Journeyer
    Currently Being Moderated

    Can anyone explain to me why, when I use this code the report takes 2 seconds to run

     

    case

    when lower('udf_'||msg.messagetype) = 'udf_distreceipt' then

    '<a class="type" href="' || 'f?p='||:APP_ID||':52:'||:APP_SESSION||'::'||:DEBUG||':RIR'||':IR_MSG_KEY,P52_PG,P51_SHOW_RESULTS:'|| lde.ldekey||'.'|| msg.alternatekey ||'.'|| msg.msgseqnumber ||','|| 50 ||','|| 1 || '">udf_distreceipt</a>'

    when lower('udf_'||msg.messagetype) = 'udf_whstatstock' then

    '<a class="type" href="' || 'f?p='||:APP_ID||':53:'||:APP_SESSION||'::'||:DEBUG||':RIR'||':IR_MSG_KEY,P53_PG,P51_SHOW_RESULTS:'|| lde.ldekey||'.'|| msg.alternatekey ||'.'|| msg.msgseqnumber ||','|| 50 ||','|| 1 || '">udf_whstatstock</a>'

    when lower('udf_'||msg.messagetype) = 'udf_distdeli' then

    '<a class="type" href="' || 'f?p='||:APP_ID||':51:'||:APP_SESSION||'::'||:DEBUG||':RIR'||':IR_MSG_KEY,P51_PG,P51_SHOW_RESULTS:'|| lde.ldekey||'.'|| msg.alternatekey ||'.'|| msg.msgseqnumber ||','|| 50 ||','|| 1 || '">udf_distdeli</a>'

    else null END table_link

     

    but when I use apex_util.prepare_url, it takes over 2 minutes

     

    case

    when lower('udf_'||msg.messagetype) = 'udf_distreceipt' then

    '<a class="type" href="' || apex_util.prepare_url('f?p='||:APP_ID||':52:'||:APP_SESSION||'::'||:DEBUG||':RIR'||':IR_MSG_KEY,P52_PG,P51_SHOW_RESULTS:'|| lde.ldekey||'.'|| msg.alternatekey ||'.'|| msg.msgseqnumber ||','|| 50 ||','|| 1, null, 'SESSION') || '">udf_distreceipt</a>'

    when lower('udf_'||msg.messagetype) = 'udf_whstatstock' then

    '<a class="type" href="' || apex_util.prepare_url('f?p='||:APP_ID||':53:'||:APP_SESSION||'::'||:DEBUG||':RIR'||':IR_MSG_KEY,P53_PG,P51_SHOW_RESULTS:'|| lde.ldekey||'.'|| msg.alternatekey ||'.'|| msg.msgseqnumber ||','|| 50 ||','|| 1, null, 'SESSION') || '">udf_whstatstock</a>'

    when lower('udf_'||msg.messagetype) = 'udf_distdeli' then

    '<a class="type" href="' || apex_util.prepare_url('f?p='||:APP_ID||':51:'||:APP_SESSION||'::'||:DEBUG||':RIR'||':IR_MSG_KEY,P51_PG,P51_SHOW_RESULTS:'|| lde.ldekey||'.'|| msg.alternatekey ||'.'|| msg.msgseqnumber ||','|| 50 ||','|| 1, null, 'SESSION') || '">udf_distdeli</a>'

    else null END table_link

     

    Thanks

     

    Gus

  • 10. Re: Interactive Report Performance With Conditional Link
    fac586 Guru
    Currently Being Moderated

    The reason for this is that Oracle is calling apex_util.prepare_url for all 127,000 rows. This incurs context switching overhead for every row, and I suspect that the apex_util.prepare_url function itself is also relatively computationally expensive, taking significant time to process each URL.

  • 11. Re: Interactive Report Performance With Conditional Link
    Gus C Journeyer
    Currently Being Moderated

    Normally I have session state protection enabled for all my pages.

    I guess the only thing to do is not to use the apex_util.prepare_url for this report and take off

    the session state protection on the pages being called by the link.

     

    Is the only other option, to force the user to filter the data before the report is displayed

     

    Gus

  • 12. Re: Interactive Report Performance With Conditional Link
    Tom Petrus Expert
    Currently Being Moderated

    Well either force them to filter first or actually remove the prepare_url call from your sql. Can you not just use the link column functionality of apex? All your links seem to differ in is in the text displayed for the anchor and not the actual url. So your link may not look as specific but at least it'll have session state protection and be speedy and everything will be rather standard. You could remove SSP of course, but is that better than a more generic link text? The call is up to you of course.

  • 13. Re: Interactive Report Performance With Conditional Link
    Gus C Journeyer
    Currently Being Moderated

    Thank you all for your input.

    I guess I have a decision to make

     

    Gus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points