This discussion is archived
8 Replies Latest reply: Apr 23, 2013 4:05 PM by Vite DBA RSS

Interactive reports performance

joelanier Newbie
Currently Being Moderated
I am at Apex 4.1, and have been using InterActive (I/R) reports for quite awhile. I try to use them as much as possible to give my users as much flexibility as possible.
Performance has always been an issue, perhaps I someone can explain if theres something I am doing wrong. Perhaps there is an article on I/R reports, how the database query and I/R filtering work together.

For discussion, lets say the report is based on a single table, called Billing, which has 600k rows.
The report has its maximum rows parameter set to 100k.
Lets say the user enters a customer_name = 'ABC', which should result in 20 rows.
If I am understanding correctly, APEX accomplishes this in two steps. 1) starts reading the 600k row table bringing all 600k back to page, then 2) applies the I/R filter to display the final 20 rows.
I believe (using traces and/or debug), this looks like a sub-select:
select * from (600 k rows from step1) where user I/R filters are true.

Questions:
1) Am I correct that the 100k row limit gets applied after step2?
2) Is there some way to apply the I/R filters at the database level, and not return the 600k rows back 1st?
3) once the 20 rows are displayed, if the user leaves the page and returns, it appears to repeat step 1 and 2 again, instead of just displaying the 20 rows, because it is very slow again.

I have used collections with I/R reports, but not in cases like this where the initial results from step 1 is over 100k rows. Is there a way to store the final 20 rows in a collection?
  • 1. Re: Interactive reports performance
    Hari_639 Guru
    Currently Being Moderated
    As I understood..

    <li> Oracle will fetch first set of records from your query based on pagination you have set
    <li> When you apply any filter, yes, I think it uses Sub QUery, but it won't execute in two steps. Oracle will re-write the query and so it can use indexes

    When you execute the query with filter out-side apex, are you getting results quicky?
    Do you have index on filtered column? Have you checked "explain plan"?

    Regards,
    Hari
  • 2. Re: Interactive reports performance
    joelanier Newbie
    Currently Being Moderated
    Let me add, that I do understand that this is essentially just select * from table, and then my users want to manipulate the data using interactive reports. Most of my pages have many table joins, but very limited criteria to limit the initial # or rows. These are more "research" pages, where once they find the desired records, then they add notes, status it, assign to others, etc... If this is the wrong application for I/R reports, then I revert to standard reports. Or perhaps add a few page level items to pass to the query, and keep the I/R report.
  • 3. Re: Interactive reports performance
    joelanier Newbie
    Currently Being Moderated
    Yes, the query running as sql navigator, Toad, etc, works fine.
    Indexing has been addressed to a degree, but as you know, the users can select any I/R column, so every column would need to be indexed. And I am not sure, but indexes may not help much if they dont use the "=" I/R operator.

    Can you tell me if the query reruns at the database level when you return to the page every time, or does it just redisplay the current page's data from cache or a temporary table;e, collection, etc? Thank you.
  • 4. Re: Interactive reports performance
    Vite DBA Pro
    Currently Being Moderated
    Hi Joe,

    in a very basic way, an IR works this way

    * It takes the original query you specified for the report and embeds that as an inline view.
    * It applies any filters and sorts etc outside this inline view as well as adding a row count select item and a maximum row count filter.
    * The database executes the query and returns the rows to the IR engine which then applies the row window and checks the maximum row count

    Every time you return to the page in the same session, the session state will remember the last state and execute the same query. In a standard installation, the only caches will be at the database level.

    The database is smart enough to merge inline views, so that filters, sorts etc will be applied when the query executes and only return that number of rows to the IR engine up to the maximum row count.

    One thing to be wary of when you test queries in tools such as Toad is that Toad will execute the query and then only fetch the first window full of rows and display them, whereas the Apex IR engine will fetch all the rows up to the maximum row count before displaying anything. This can give the false impression that Toad (or any other modern sql tool) is executing the query much faster. For performance then, the maximum row count can be significant.

    For indexing, it's not considered good practice to index all columns. You will find that users will tend to use a much smaller set of columns to do their searches and the database will consider more than just the "=" operator when deciding whether to use indexes. The problem with indexes is that while they can benefit query speed, the overhead to maintain them can affect other operations such as DML, not to mention they consume space in the database.

    The basic strategy that I use is to go with the indexes that come with applying primary and unique constraints. The next step is to consider foreign keys that are frequently used in joins and consider indexing those. There is a theory going around that all FK's should be indexed, but I don't subscribe to this and it's an argument for another day. Finally you should look at how the application is executing queries and consider any other indexes. Indexing can be a complex field with many options available, such as index organised tables, bitmap indexes, function based indexes, multi-column indexes and even using partitioning as a form of indexing. The main thing to remember is that for indexes to be considered usefull by the database they must be selective to low single digit percentage and queries should be written in such a way as to take advantage of them.

    All this is fairly high level, but I hope it helps.

    Regards
    Andre
  • 5. Re: Interactive reports performance
    Hari_639 Guru
    Currently Being Moderated
    Can you tell me if the query reruns at the database level when you return to the page every time, or does it just redisplay the current page's data from cache or a temporary table;e, collection, etc?
    As per my understanding, in Oracle, when you run the same query again and again, then it won't parse the query every time, however Oracle won't cache the SQL results by default. So in theory, it fetches the results every time. But if same data blocks are available in memory, then it may fetch from memory.

    If you want to cache SQL query results then this can be done from Oracle 11G R1 or later. See this

    And please note that APEX won't cache any pages (server side) unless you specify it. Every time you run the page, then it will generate page dynamically.
    Indexes
    Yes, you can't create indexes on all columns, but you should create indexes on some columns which you know users will user for filtering regularly.

    We have an IR with simple query as 'select * from our_table' and it has around 200K rows. When I apply any filter (non-indexed), it returns the results just in few seconds. So I suggest you

    <li> check explain plan and see if you can reduce cost
    <li> Ask users to use pagination with less than 100 rows per page. If they specify "display 1000 rows per page", then it may take time to load data into page.

    Regards,
    Hari

    Edited by: Hari_639 on Apr 23, 2013 11:54 AM
  • 6. Re: Interactive reports performance
    joelanier Newbie
    Currently Being Moderated
    Thanks for both of your help.
    We have addressed the basic indexing before, and many of these pages are 1-3 table joins using FKs, but many times outer joins. I will ask a team member to review the sql again.
    I think it comes down to the sheer amount of data my users insist they need to see. I set pagination to 50, sometimes 100 rows, with a max of 100k ,because they insisted, and I couldn't persuade them lower.
    I am completely sold on IR reports, but it still seems they carry some inherent overhead. If I create a non-IR clone page, it just seems to run faster.
    I am also considering having the users review, say a 10 column report, and pick the 2-3 columns they generally filter by, then add them as page items (dropdowns, etc), so that I can refer to them directly, as part of report (resulting inline) sql. Your answer below indicates that shouldnt make any difference, perhaps I need to write a sample query both ways in Toad and review the explain plans.

    I now understand IR reports better, thank you! Is there a white paper on specifically IR reports, how they internally work with the database, etc? I still would like to know what is actually happening when the user "applies" a new filter, does it rerun the entire query at the database every time. That can be frustrating experience the the query seems to "run" every time as they are trying to add 4 filters... Seems that a "apply" (filter) and then a Run/Rerun/Go button would work better (at times).

    Thanks again. Like I said, I love IR reports and use them extensively, but would like to improve the user experience where ever I can.
  • 7. Re: Interactive reports performance
    maceyah Journeyer
    Currently Being Moderated
    This is to elaborate on Andre's comments: This example (4.0.2) shows the query Apex runs for a simple IR against EMP table, having "Row ranges X to Y of Z" pagination scheme, where a simple search is entered in the std search field, and I've sorted on ENAME in the IR. Note the apxws_row_cnt as well as the case-insensitive search of each field using INSTR (these predicates wont use any indexes you may have). Notice how the stop-key APXWS_MAX_ROW_CNT is applied after the filtering, and the ENAME sort is the final step.

    Sometimes Apex seems to use FIRST_ROWS optimizer mode rather than ALL_ROWS, and this becomes my prime spect when I experience a big performance difference between Toad (or whatever) vs Apex, although Andre's comment about Toad etc only retrieveing part of the resultset is spot on too. You can try adding ALL_ROWS hint to your Apex query, but I prefer adding something like "execute immediate 'alter session set optimizer_mode = all_rows';" to an "On Load - Before Header" pl/sql page level process, because the optimizer behavior isn't well defined when your hint is effectively in an inline view.

      SELECT apxws_row_pk, "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO",
                   COUNT (*) OVER () AS apxws_row_cnt
        FROM (SELECT *
                FROM (SELECT b.ROWID apxws_row_pk, b.*
                        FROM (SELECT *
                                FROM (SELECT "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO"
                                        FROM "EMP")) b) r
               WHERE ( (   INSTR (UPPER ("EMPNO"), UPPER (:APXWS_SEARCH_STRING_1)) > 0
                        OR INSTR (UPPER ("ENAME"), UPPER (:APXWS_SEARCH_STRING_1)) > 0
                        OR INSTR (UPPER ("JOB"), UPPER (:APXWS_SEARCH_STRING_1)) > 0
                        OR INSTR (UPPER ("MGR"), UPPER (:APXWS_SEARCH_STRING_1)) > 0
                        OR INSTR (UPPER ("SAL"), UPPER (:APXWS_SEARCH_STRING_1)) > 0
                        OR INSTR (UPPER ("COMM"), UPPER (:APXWS_SEARCH_STRING_1)) > 0
                        OR INSTR (UPPER ("DEPTNO"), UPPER (:APXWS_SEARCH_STRING_1)) > 0))) r
       WHERE ROWNUM <= TO_NUMBER (:APXWS_MAX_ROW_CNT)
    ORDER BY "ENAME", apxws_row_pk
    I often also cache the resultset in a collection for faster IR pagination when the base query is slow, and the resultsets are no more than a few thousand rows. I've also created some very effective partial-refresh classic reports, utilizing search-as-you-type auto-complete, and dynamic queries constructed based on the specific filters used, that were more usable and much better performing than IR reports, so dont discount them completely.
  • 8. Re: Interactive reports performance
    Vite DBA Pro
    Currently Being Moderated
    Hi Joe,

    have a look at maceyah's post for some more indepth info on IR's.

    In answer to your later questions
    I am also considering having the users review, say a 10 column report, and pick the 2-3 columns they generally filter by, then add them as page items (dropdowns, etc), so that I can refer to them directly, as part of report (resulting inline) sql. Your answer below indicates that shouldnt make any difference, perhaps I need to write a sample query both ways in Toad and review the explain plans.
    Don't forget that users can save reports if they find that they are running the same or similar reports all the time.

    Also, in order to get an accurate execution plan, you should be explaining the queries as they are presented to the database. If you run the page in debug mode you can usually get the query as it is generated by the IR engine. You can also trace at the database level to see exactly what the queries are.
    I still would like to know what is actually happening when the user "applies" a new filter, does it rerun the entire query at the database every time.
    Unfortunately, every time you apply a new filter, the query gets executed. (perhaps an enhancement request couuld be made to allow for multiple filters to be applied in one action) This is because of the architecture of Apex. Apex executes in the database. At its most basic level, Apex is a database application made up of all the usual database objects; users, tables, indexes, packages, procedures, functions, etc. These use Oracle's web application architecture to serve results to a request by a user through a browser as HTML. This means that when a user applies a new filter and submits the request, the IR engine, modifies the query, re-executes it and servers the resulting HTML back to the browser.

    This is different to some data analysis tools, which will take a base copy of the data and copy this to the client machine and then allow the user to slice and dice it how they want.

    Both approaches have their pros and cons, but personally I would prefer to go with the Apex approach, as you know you are always dealing with current data and the client footprint is much smaller. Understanding these differences means you can then apply techniques and strategies which minimise the cons.

    Regards
    Andre

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points