2 Replies Latest reply on Nov 19, 2018 3:17 PM by Sandijs J.

    Expected load time for Interactive Grid?

    Sandijs J.

      Hi,

       

      What is considered to be a good performance/loading time for Apex page with an Interactive Grid (editable)?

       

      I'm working on IG with ~ 40 visible columns, only 14 of those are editable and all 14 are Switch columns. Query returns 42 more columns which are hidden and are referenced only in JavaScript.

       

      IG query is very simple and it executes in database under second. Yes - I have enabled SQL tracing for my Apex page, I have run tkprof on trace file and statistics for IG query are these:

       

      call     count       cpu    elapsed       disk      query    current        rows

      ------- ------  -------- ---------- ---------- ---------- ----------  ----------

      Parse        1      0.01       0.01          0          0          0           0

      Execute      1      0.00       0.00          0          0          0           0

      Fetch       52      0.08       0.08          0        347          0          51

      ------- ------  -------- ---------- ---------- ---------- ----------  ----------

      total       54      0.10       0.10          0        347          0          51

       

      But when the page loads, actual load time for page is about 2.8 seconds.

       

      When I look at Debug for the page, I see that most of the time is spent on:

      1) Perform an Equal join (there are 14 line pairs  "Parse LOV query ..." and "...Perform an equal join..." - one for each Switch column. First ones are very fast, last one is 0.75 seconds in this case;

      2) SQL Statement prepared 0.297 seconds;

      3) Rewrite SQL takes 0.63 seconds;

      4) SQL Statement prepared one more time and it takes 0.9 seconds;

      Screenshot 2018-11-17 13.37.13.png

       

      I tested that very simple page with IG based on EMP table and 14 records loads in 0.11 seconds in my dev environment.

      If I replace Switch column type with Text or display only for my IG, I can improve load time to 1.6 seconds but I do think it is very slow, given that SQL query in the database runs in fraction of that time.

       

      I'm trying to understand if this is expected behavior or I can do anything to improve the load time for my page?

       

      I'm running Apex 18.1 on Oracle Database Express Edition 18c ...

        • 1. Re: Expected load time for Interactive Grid?
          fac586

          Sandijs J. wrote:

           

          What is considered to be a good performance/loading time for Apex page with an Interactive Grid (editable)?

          It depends. A 2 second maximum render time is my personal performance yardstick for any APEX page, irrespective of the components used. This has also been the response time mandated by the SLAs on a couple of projects I have worked on.

          I'm working on IG with ~ 40 visible columns, only 14 of those are editable and all 14 are Switch columns.

          In the absence of more detailed information my initial response would be that from a usability perspective 40 columns in one report is information overload. Similarly, 14 identical controls per row is potentially confusing for users. More information about your use case is required.

          Query returns 42 more columns which are hidden and are referenced only in JavaScript.

          Hidden how? And in what way are they referenced in JavaScript?

          IG query is very simple and it executes in database under second. Yes - I have enabled SQL tracing for my Apex page, I have run tkprof on trace file and statistics for IG query are these:

           

          call count cpu elapsed disk query current rows

          ------- ------ -------- ---------- ---------- ---------- ---------- ----------

          Parse 1 0.01 0.01 0 0 0 0

          Execute 1 0.00 0.00 0 0 0 0

          Fetch 52 0.08 0.08 0 347 0 51

          ------- ------ -------- ---------- ---------- ---------- ---------- ----------

          total 54 0.10 0.10 0 347 0 51

           

          But when the page loads, actual load time for page is about 2.8 seconds.

           

          When I look at Debug for the page, I see that most of the time is spent on:

          1) Perform an Equal join (there are 14 line pairs "Parse LOV query ..." and "...Perform an equal join..." - one for each Switch column. First ones are very fast, last one is 0.75 seconds in this case;

          14 switch columns and 14 LOV queries: is this connected? Are these queries only being executed for the implicit Yes/No LOVs in the switch columns or are there other LOV-based columns in the grid?

           

          If only the switch columns are involved, it seems strange that one would take any longer than the others. Is this consistently reproducible?

           

          What queries does the trace show being executed in connection with these debug events?

          2) SQL Statement prepared 0.297 seconds;

          3) Rewrite SQL takes 0.63 seconds;

          4) SQL Statement prepared one more time and it takes 0.9 seconds;

          Screenshot 2018-11-17 13.37.13.png

           

          I tested that very simple page with IG based on EMP table and 14 records loads in 0.11 seconds in my dev environment.

          If I replace Switch column type with Text or display only for my IG, I can improve load time to 1.6 seconds but I do think it is very slow, given that SQL query in the database runs in fraction of that time.

          Are you timing the base IG query or the rewritten one generated by APEX? Compare the timings and execution plans for both. Use debug LEVEL9 to get the full rewritten query.

           

          Always post APEX and database debug/traces information as plain text, not screenshots.

          I'm trying to understand if this is expected behavior or I can do anything to improve the load time for my page?

          More information is required. Describe the requirements, not the immediate problem. A bit of background information to give things some context goes a long way.

          • 2. Re: Expected load time for Interactive Grid?
            Sandijs J.

            I'm working on order processing page where some basic information for order is displayed like Order ID, Order date, Customer name, Customer Address etc.

            Each order can contain 0 to 14 different components, each of the components has an Y/N indicator if specific component is included in the order, Status (In progress, Completed - displayed as a Switch field), responsible person and date when this component was completed.

             

            I want to provide users possibility to look up the order using IG and mark completed components with single click. User should only be allowed to edit those components where he is "responsible person", but should be able to see status of each order/component.

             

            Hidden how? And in what way are they referenced in JavaScript?

             

            Column type is set to "Hidden". Values are referenced to have custom "highlight" rules (actually to disable cells and hide content where I want to hide it). Solution taken from Re: Interactive Grid conditional highlighting

             

            14 switch columns and 14 LOV queries: is this connected? Are these queries only being executed for the implicit Yes/No LOVs in the switch columns or are there other LOV-based columns in the grid?

             

            Apex creates LOV query for every switch column.

             

            If only the switch columns are involved, it seems strange that one would take any longer than the others. Is this consistently reproducible?

             

            Yes, it is reproducible.

            I created test on apex.oracle.com, created grid with 11 Switch columns named YESNO1 ... YESNO11.

             

            Timings for columns YESNO1 ... YESNO10 are similar and "equal join" part takes about 0.00008 seconds but for column YESNO11 it is 0.01234 seconds.

            After reviewing trace files, I think that actually  when the apex says it is "performing an equal join" for last Switch column, it is parsing whole rewritten SQL.

            Still I'm not happy with Parse witch takes 0.7 seconds of time in Apex when the statement is parsed in database thousands of times faster ...

             

            0.222440.00030
            Parse LOV query of column YESNO10 to get display and return column for joining it to base query.
            4

            0%

            0.222740.00008
            ...Perform an equal join. Reason: Column is required = false, LOV display extra = false, LOV display null = false
            4

            0%

            0.222810.00033
            Parse LOV query of column YESNO11 to get display and return column for joining it to base query.
            4

            0%

            0.223140.01234
            ...Perform an equal join. Reason: Column is required = false, LOV display extra = false, LOV display null = false

             

             

            Are you timing the base IG query or the rewritten one generated by APEX? Compare the timings and execution plans for both. Use debug LEVEL9 to get the full rewritten query.

             

            I am timing and analyzing full rewritten IQ queries (not the one I have entered in Apex for my IG) in database and as I told - rewritten query is parsed, executed and 51 rows fetched in 0.1 seconds at most.

             

            I don't need to use debug LEVEL 9 as I have all the SQL's for page load in SQL trace files generated "p_trace=YES".