13 Replies Latest reply on Aug 13, 2018 9:10 AM by CJ Bell

    Interactive Report performance ???

    CJ Bell

      We are currently prototyping our application in Apex and everything is going great with the user feedback

       

      We recently loaded 250,000 records to a table and now the apex interactive report on that page takes over 9 seconds to display which is not really acceptable to the users

       

      I have set the max rows to 5000 but this doesn't seem to help , we are running APEX currently on Oracle XE while we develop and I am going to evaluate Oracle Cloud offering to see what the performance is like there

       

      its a simple select on 2 joined tables , the other table has 3 rows in it that just defines permissions as to which rows can be visible to the :APP_USER

       

      I thought APEX just did an initial quick fetch ??

        • 1. Re: Interactive Report performance ???
          fac586

          CJ Bell wrote:

           

          We are currently prototyping our application in Apex and everything is going great with the user feedback

           

          We recently loaded 250,000 records to a table and now the apex interactive report on that page takes over 9 seconds to display which is not really acceptable to the users

           

          I have set the max rows to 5000 but this doesn't seem to help , we are running APEX currently on Oracle XE while we develop and I am going to evaluate Oracle Cloud offering to see what the performance is like there

           

          its a simple select on 2 joined tables , the other table has 3 rows in it that just defines permissions as to which rows can be visible to the :APP_USER

           

          I thought APEX just did an initial quick fetch ??

          APEX wraps interactive report source queries inside other queries to support the various IR features. This may affect the execution plan used for the query (particularly when statistics on the data sources do not exist or are out of date).

           

          Run the page in debug mode, extract the generated IR query from the debug log, get an execution plan from SQL*Plus or SQL Developer, and post them here (as text, not screenshots).

          • 2. Re: Interactive Report performance ???
            Scott Wesley

            And what's your pagination setting?

             

            The "of Z" can be a performance killer with larger data sets.

            Try the same thing in SQL Developer. Query your 250k row table, then press ctrl-down in your results. How long did that take?

            • 3. Re: Interactive Report performance ???
              Mahmoud_Rabie

              Hi CJ Bell

               

              I think partitioning the table could help you in performance optimization

              https://livesql.oracle.com/apex/livesql/file/tutorial_EDVE861H5MO8DX16EGJ80HOTK.html

               

              I hope that helps

               

              Regards

              Mahmoud

              • 4. Re: Interactive Report performance ???
                fac586

                Mahmoud_Rabie wrote:

                 

                I think partitioning the table could help you in performance optimization

                Why? There is no indication from the information provided that the data involved includes suitable partitioning keys, or that the queries executed by the report would result in the optimizer choosing execution plans that make use of partition pruning. It's highly unlikely that partitioning a data set containing only 250,000 rows would yield any tangible performance benefit.

                 

                Instead of proposing random solutions, the first priority is to fully understand the the problem by applying some instrumentation to find out what is actually happening.

                • 5. Re: Interactive Report performance ???
                  CJ Bell

                  Sorry , I tried to get it as text , HTML and it just wouldn't format correctly

                   

                  select

                         "LINK",

                         "PRETTY_DATE",

                         "FORMFIELDS",

                         "PAGE",

                         "VISITORID",

                         count(*) over () as apxws_row_cnt

                  from (

                  select  *  from (

                  select VISITID LINK,

                         PRETTY_DATE,

                         FORMFIELDS,

                         PAGE,

                         VISITID,

                         VISITORID,

                         EMAIL

                  from FORM_submissions_TFS fs,

                  USERS U,

                  USER_SITES US

                  where u.id = us.userid

                  and fs.siteid = us.siteid

                  and upper(u.username) = :APP_USER

                  and fs.siteid = us.siteid

                  --order by visitid desc

                  )  r

                  ) r where rownum <= to_number(:APXWS_MAX_ROW_CNT)

                   

                   

                  • 6. Re: Interactive Report performance ???
                    fac586

                    CJ Bell wrote:

                     

                    Sorry , I tried to get it as text , HTML and it just wouldn't format correctly

                     

                    select

                    "LINK",

                    "PRETTY_DATE",

                    "FORMFIELDS",

                    "PAGE",

                    "VISITORID",

                    count(*) over () as apxws_row_cnt

                    from (

                    select * from (

                    select VISITID LINK,

                    PRETTY_DATE,

                    FORMFIELDS,

                    PAGE,

                    VISITID,

                    VISITORID,

                    EMAIL

                    from FORM_submissions_TFS fs,

                    USERS U,

                    USER_SITES US

                    where u.id = us.userid

                    and fs.siteid = us.siteid

                    and upper(u.username) = :APP_USER

                    and fs.siteid = us.siteid

                    --order by visitid desc

                    ) r

                    ) r where rownum <= to_number(:APXWS_MAX_ROW_CNT)

                     

                     

                     

                    1. Are the statistics on these tables current? The OP mentioned loading 250,000 rows, but the plan refers to ~350,000. Were there already 100,000 rows in the table?

                     

                    2. How many rows in USERS and USER_SITES?

                     

                    3. What indexes exist on the FORM_SUBMISSIONS_TFS, USERS, and USER_SITES tables?

                    1 person found this helpful
                    • 7. Re: Interactive Report performance ???
                      Pavel_p

                      Hi,

                      does your IR by any chance contain any column(s) of type Plain Text (based on List of Values)? If so, please remove such columns and perform the join in underlying select instead.

                      How long does it take to run the wrapped select in SQL Developer?

                      Regards,

                      Pavel

                      • 8. Re: Interactive Report performance ???
                        fac586

                        Pavel_p wrote:

                         

                        does your IR by any chance contain any column(s) of type Plain Text (based on List of Values)?

                        No. These would show in the query posted above, which can be seen to be that executed for the IR by the presence of the apxws_row_cnt column and where rownum <= to_number(:APXWS_MAX_ROW_CNT) predicate.

                         

                        IR LOV look-up queries are implemented using joins, so their performance is generally indistinguishable from that of manual joins in the report source. Where LOV look-ups really hit performance is on classic reports that display a lot of rows, as the LOV query is executed repeatedly for each row displayed.

                        • 9. Re: Interactive Report performance ???
                          CJ Bell

                          sorry for the late reply

                           

                          the table grows over time , there are over 400k rows now

                           

                          there are 3 rows in USERS and 3 rows in USER_SITES

                           

                          no indexes on any of the tables

                          • 10. Re: Interactive Report performance ???
                            fac586

                            CJ Bell wrote:

                             

                            sorry for the late reply

                             

                            the table grows over time , there are over 400k rows now

                             

                            there are 3 rows in USERS and 3 rows in USER_SITES

                             

                            no indexes on any of the tables

                            Ensure that there are primary and foreign keys defined on all of the tables, and create indexes on the FK columns.

                            • 11. Re: Interactive Report performance ???
                              CJ Bell

                              Sorry that is all on already , I thought you meant extra indexes

                              • 12. Re: Interactive Report performance ???
                                fac586

                                CJ Bell wrote:

                                 

                                Sorry that is all on already , I thought you meant extra indexes

                                What about statistics? What's the result of

                                 

                                select table_name, num_rows from all_tables where table_name in ('USERS', 'USER_SITES', 'FORM_SUBMISSIONS_TFS')

                                 

                                and

                                 

                                select index_name, index_type, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor from all_indexes where table_name = 'FORM_SUBMISSIONS_TFS'

                                 

                                What's the distribution of SITEID values in FORM_SUBMISSIONS_TFS?

                                 

                                select siteid, count(*) n from form_submissions_tfs