13 Replies Latest reply: Sep 11, 2013 3:44 AM by Gus C RSS

    Interactive Report Performance With Conditional Link

    Gus C

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  Thank you all for your input.

                                  I guess I have a decision to make

                                   

                                  Gus