9 Replies Latest reply: Jun 25, 2014 12:34 AM by scott.wesley RSS

    Performance with Named Column (row template)

    scott.wesley

      Hi gang,

       

      I'm a fan using the Named Column (row template) solution, particularly since it facilitates row level customisation much better than detail view interactive reports.

      For added context, this is what I'm referring to

      APEX Reports: Custom Layout with Named Column (Row Template)

       

      When using only 1 row template I have no issues at all, but when I experience immediate performance issues when I include a 2nd row template and apply a row template expression such as

      '#ID#' is null

       

      I realise the expression differs from the documented examples :

      #SAL# > 1000

      '#ENAME#' = 'SMITH'

      but 4.2.0 gives me errors when I don't supply quotes.

       

      This is for a basic query with approx 100 rows, displaying all on the page.

       

      All the posts I see regarding this template style revolve around getting it working.

      Has anyone else experienced this performance issue? and maybe have a suggested solution (that doesn't include paginating the page)?

       

      ~Scott

        • 1. Re: Performance with Named Column (row template)
          fac586

          scott.wesley wrote:

           

          I'm a fan using the Named Column (row template) solution, particularly since it facilitates row level customisation much better than detail view interactive reports.

          For added context, this is what I'm referring to

          APEX Reports: Custom Layout with Named Column (Row Template)

           

          When using only 1 row template I have no issues at all, but when I experience immediate performance issues when I include a 2nd row template and apply a row template expression such as

          '#ID#' is null

           

          I realise the expression differs from the documented examples :

          #SAL# > 1000

          '#ENAME#' = 'SMITH'

          but 4.2.0 gives me errors when I don't supply quotes.

          That's understandable in this context. Row template expressions use static text substitution for the column values so when the ID column value is null

           

          #ID# is null

           

          would be evaluated as

           

          is null

           

          which is clearly a syntax error.

           

          '#ID#' is null

           

          is evaluated as

           

          '' is null

           

          which is syntactically valid.

          This is for a basic query with approx 100 rows, displaying all on the page.

           

          All the posts I see regarding this template style revolve around getting it working.

          Has anyone else experienced this performance issue? and maybe have a suggested solution (that doesn't include paginating the page)?

          As is obvious from those posts, I am also a big fan of custom row report templates, and have used them extensively. I don't recall encountering any performance problems with them, even when using the maximum 4 conditional row templates and/or no pagination.

           

          Can you provide more information on the background to this regarding the report query, templates and reason for no pagination? Is the report pagination scheme set to "None" and have all of the pagination substitution strings removed from the template?

           

          Will the report always return only around 100 rows?

           

          Please quantify the performance problem with debug traces for the report when using 1 and 2 row templates.

          • 2. Re: Performance with Named Column (row template)
            jrimblas

            I'm a big fan of Named Column templates also.  I don't recall ever noticing a performance problem (at least none where the SQL wasn't the culprit).

            • 3. Re: Performance with Named Column (row template)
              scott.wesley

              This was Friday arvo and I wanted to get the question out there, so I didn't have a chance to recreate online.

               

              I just tried a simple generic example but could not recreate the issue.

               

              The business case is a simple master-detail-detail with all up < ~2000 rows before master_id restriction, but we've been toying with these for a few reports and noticed an immediate slow down when the row template pl/sql expression was added.

              All for basic big-picture read only display.

               

              I'll attempt to re-create an example on Monday, but thanks for the description of the expression. It makes immediate sense when you put it like that.

               

              My hunch - a moderately compex query (for layout purposes, though still just a union with basic joins) on basic small data is getting lost in translation for the template. I might snapshot the data in a materialised view and see if the problem remains.

              I'll also check if problem still evident (though scaled down) when paginated.

               

              But if you've all played with these templates and not noticed this sort of problem, then that's good information to have as well - thanks.

              • 4. Re: Performance with Named Column (row template)
                scott.wesley

                It seems I did manage to re-create the issue, though it's affects are a little more subtle on apex.oracle.com

                https://apex.oracle.com/pls/apex/f?p=73000:32

                 

                The timing for the classic report is 4-5 quicker than the custom template version of the same query (select .. from TAB)

                 

                My business case shows the same symptoms, regardless of what I do with the template conditions - and there is certainly nothing wrong with the query - I did a CTAS on my content, so the report shows all 150 records from a simple table - yet takes ~10 seconds to render.

                This is all due to dynamic PL/SQL, eg

                Execute Statement: begin wwv_flow.g_boolean := 'TABLE' = 'TABLE'; end;

                the same issue I blogged about a while ago in regard to conditions.

                 

                On apex.oracle.com, these evalutations take 0.00030 - on the client server it typically takes 0.03100.

                So multipled by 150 rows, that's 5 seconds right there. The debug output starts at 0.62s for the select statement, and final row conditions is 10.06s.

                 

                Is there a more effective solution for the row template expressions?

                • 5. Re: Performance with Named Column (row template)
                  scott.wesley

                  The same issue is prevalent in 5.0 EA2

                  https://apexea.oracle.com/pls/apex/f?p=188:1

                   

                  And there sure was a pleasant experience creating that in 5.0 over 4.2 - much faster!

                  • 6. Re: Performance with Named Column (row template)
                    fac586

                    scott.wesley wrote:

                     

                    On apex.oracle.com, these evalutations take 0.00030 - on the client server it typically takes 0.03100.

                    Firstly that's quite a big difference. Secondly, 0.031s to evaluate a single condition isn't too impressive.

                     

                    Have you tried creating a function to test the IS NULL condition statically for comparison?

                    • 7. Re: Performance with Named Column (row template)
                      scott.wesley

                      Impressive or not (and this is not on production db), relatively speaking the named column template takes longer to run due to these tests.

                       

                      I'm not sure this statement on the APEX end can be avoided, so regardless of how it's formatted there is this extra overhead.

                      When using a function that returns boolean debug shows this, and still takes it's sweet time.

                      Execute Statement: begin wwv_flow.g_boolean := fn_ret_boolean(123); end;

                      • 8. Re: Performance with Named Column (row template)
                        fac586

                        scott.wesley wrote:

                         

                        Impressive or not (and this is not on production db), relatively speaking the named column template takes longer to run due to these tests.

                         

                        I'm not sure this statement on the APEX end can be avoided, so regardless of how it's formatted there is this extra overhead.

                        When using a function that returns boolean debug shows this, and still takes it's sweet time.

                        Execute Statement: begin wwv_flow.g_boolean := fn_ret_boolean(123); end;

                        Doh. Of course. Not enough coffee...

                        • 9. Re: Performance with Named Column (row template)
                          scott.wesley

                          Work around for the time being is to copy the HTML from the custom template driven report into a dynamic PL/SQL block.

                           

                          It's only 80 lines of code, quarter of which is the query - but it's a shame I can't use the APEX functionality.

                           

                          Performance back to 0.02s for the region.