8 Replies Latest reply on Mar 6, 2014 11:52 PM by Mike Kutz

    Interactive report performance issue


      We are trying to run a report which is based on a table which has around 2Lakh records. Issues encountered are given below:


      1. It is taking lot of time to load the report

      2. Throwing "500 Internal server error"

      3. Unable to download report.

      4. Partial report downloaded sometimes


      Any suggestions would be helpful.


      APEX version :

        • 1. Re: Interactive report performance issue

          Hi, PT


          2Lakh = 200 000? May be it's too much?

          Set filter for your data or create report on server (for example, save it as clob) and then let user download it.

          • 2. Re: Interactive report performance issue

            we have million of record(3ml) in IR report with one blob(download) column it takes 1.5min,its giving result! search also same.compared to other Tech Apex IR is good!


            2Lc nothing !!!!



            Application server(?) and RAM/Network/Table structure need to be analysis!

            btw: send the server details SW/HW as well!

            • 3. Re: Interactive report performance issue

              We are using APEX Listener

              RAM around : 94GB

              Please let us know if any workaround to get this report to work.

              • 4. Re: Interactive report performance issue
                Vite DBA



                There is no work around, when it all boils down, an IR report is a SQL query executing in the database. Treat this as a database performance issue. Find out the query being issued and work out why it is not executing as quick as it should. There are lots of posts and resources on performance tuning. BTW, Lakh is a unit in a south Asian specific numbering system  equal to 100,000 (thanks Google), As this is a forum open world wide, it would be best to use the more widely accepted Arabic numbering system.



                • 5. Re: Interactive report performance issue

                  Its a simple select query on a table (no where clause or anything). It is faster when we query directly (sql plus etc).

                  When we run the IR report it is not working.

                  • 6. Re: Interactive report performance issue
                    William Wallace

                    How many rows are you trying to fetch? Table size isn't the problem but rather how much rows you are trying to extract.

                    • 7. Re: Interactive report performance issue
                      Vite DBA

                      Its difficult to provide anything more than general help when all you are providing is general information.


                      One of the performance traps that people fall into when they compare the performance of a query as specified in an IR and the same query in a tool such as SQL*Plus is that they assume the same query is being executed. What you should be aware of is that an IR modifies the query before executing it, in order to add the extra IR features such as display columns, ordering, functions etc. It does this by taking the original query and treating it as an in-line view, and then adds the extra bits around it. Therefore, in order to analyze the performance of an IR, you need to be aware of the exact query it is executing. This can usually be sourced through running the page in debug, or the actual query can be sourced from the database directly. This can then be analyzed with the normal DB performance tools, and if it is not the problem, then you know to start looking elsewhere. This is why I mentioned in my previous post to find out the query being issued.




                      • 8. Re: Interactive report performance issue
                        Mike Kutz

                        Has anybody thought to ask the OP:

                        What do the logs say about the error?


                        PT wrote:

                        2. Throwing "500 Internal server error"