5 Replies Latest reply: Jan 30, 2013 8:03 AM by fac586 RSS

    IR Performance - Maximum Row Count

    971271
      I understand the Maximum Row Count limits the number of rows returned from a query. My question is assuming I set "Maximum Row Count" to 100000, Does this mean that if a IR accesses a table with the 100000 rows, all the rows will be loaded into memory. I am just concerned with Performance issues, if this is the case.
        • 1. Re: IR Performance - Maximum Row Count
          jariola
          Hi,

          Here is similar post that might help
          Re: Preventing apex from fetching all rows of a sql statement

          Regards,
          Jari
          -----
          My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
          Twitter: http://www.twitter.com/jariolai
          • 2. Re: IR Performance - Maximum Row Count
            971271
            Hi Jarola

            This is our scenario. My query is quite straightforward:

            SELECT * FROM TABLE;

            There is 100,000 rows in the table.

            I have set the MAX_ROW_COUNT to 10,000 for testing.
            Pagination set to it´s "row ranges x to y

            The report is taking forever to load.
            • 3. Re: IR Performance - Maximum Row Count
              fac586
              >

              Always provide at least the following information when asking a question:

              <li>Full APEX version
              <li>Full DB/version/edition/host OS
              <li>Web server architecture (EPG, OHS or APEX listener/host OS)
              <li>Browser(s) and version(s) used

              What do you mean by "all the rows will be loaded into memory"? Loaded into what memory?
              This is our scenario. My query is quite straightforward:

              SELECT * FROM TABLE;

              There is 100,000 rows in the table.

              I have set the MAX_ROW_COUNT to 10,000 for testing.
              Pagination set to it´s "row ranges x to y

              The report is taking forever to load.
              Define "forever": Does it display at all? What are the timings shown in a debug trace?

              How many rows are set to be displayed by the IR?

              Post an explain plan for this statement:
              {code}
              SELECT * FROM TABLE;
              {code]
              • 4. Re: IR Performance - Maximum Row Count
                971271
                I am still struggling with an IR that is is taking very long to load. When I run the query in TOAD execution time is under 0.5 secs. There are ~85,000 rows in the table

                I hope I can provide all the details below:

                Full APEX version : 4.2.0
                Full DB/version/edition/host OS : ORACLE 11g
                Web server architecture (EPG, OHS or APEX listener/host OS) : APEX listener
                Browser(s) and version(s) used : IE, Chrome, Firefox

                SQL
                select s.*, l.protocolprofilename
                from CORD7NB_STATS_BQ_DLY s , NSN_SIGLINK_DIM l
                where
                s.node=l.ne
                and s.linksetname=l.linksetname
                and s.linkcode=l.linkcode
                and l.CURRENT_IND='Y'
                and l.protocolprofilename='ITUBASIC64'

                I cannot easily post the explain plan for the query.

                I have set the MAX ROW COUNT to 100000 and the reports take upto 10 minutes to load

                Edited by: hmadhi on Jan 30, 2013 3:12 PM
                • 5. Re: IR Performance - Maximum Row Count
                  fac586
                  hmadhi wrote:
                  I am still struggling with an IR that is is taking very long to load. When I run the query in TOAD execution time is under 0.5 secs. There are ~85,000 rows in the table

                  I hope I can provide all the details below:

                  Full APEX version : 4.2.0
                  Full DB/version/edition/host OS : ORACLE 11g
                  Web server architecture (EPG, OHS or APEX listener/host OS) : APEX listener
                  Browser(s) and version(s) used : IE, Chrome, Firefox
                  Always post code (and sample output, explain plans etc) using <tt>\
                  ...\
                  </tt> tags.
                  SQL
                  select s.*, l.protocolprofilename
                  from CORD7NB_STATS_BQ_DLY s , NSN_SIGLINK_DIM l 
                  where 
                  s.node=l.ne 
                  and s.linksetname=l.linksetname
                  and s.linkcode=l.linkcode 
                  and l.CURRENT_IND='Y'
                  and l.protocolprofilename='ITUBASIC64' 
                  I cannot easily post the explain plan for the query.
                  Then no one can provide any effective help, except for random guesswork.

                  {message:id=9360003}