9 Replies Latest reply: Jan 17, 2014 12:44 PM by San RSS

Interactive Report general Search is taking long time

San Explorer
Currently Being Moderated

Hi All,

 

I have two IR reports in my Application, Both are using same tables , same where clause, displaying column is different.

 

Both query returning 60000 records, In my first report , after my report gets loaded , i am using the general search button for getting some data,

 

when i try to find 100 in my reports , my first report displaying the search result in 5 seconds, But my second report i am doing the same steps , but its taking long time to respond.

 

Could some one tell me what is the cause for this problem also in what basis this search will work means how this will work?

 

Cheers,

San

  • 1. Re: Interactive Report general Search is taking long time
    Vite DBA Pro
    Currently Being Moderated

    Hi San,

     

    perhaps you should have a look at this.

     

    When your query takes too long ...

     

    The main point from this is that there is a well understood methodology and set of tools for diagnosing and resolving performance related database issues. What you have posted tells us nothing of the specific problem and only raises more questions than answers. I would suggest you read this and other related posts and then add more specific and relevant information to this post.

     

    regards

    Andre

  • 2. Re: Interactive Report general Search is taking long time
    San Explorer
    Currently Being Moderated

    Thanks for your reply. Mean time , i am not asking any query performance here. my query is taking 1 sconds to execute first 50 records.(I am Ok)

     

    The problem here is why the same query working differently in 2 IR.

     

    MY IR displaying the records quickly, while i am doing the search with IR , its not displaying .

     

    If you know just tell me , how IR search will work?

     

    Cheers,

    San

  • 3. Re: Interactive Report general Search is taking long time
    Mike Kutz Expert
    Currently Being Moderated

    APEX 'adjusts' the SQL statement so that it can do pagination.

    As such, the actual query that APEX runs is dependent on the IR settings... including which columns are displayed.

     

    Although the 'Source' is identical, the actual SQL statements being used are not the same.

     

    You can extract the actual SQL statement from the DEBUG log.  Use those SQL statements to 'tune' your SQL.

    I recommend using Oracle SQL Developer (free) for taking a look at the PLAN.

     

    Although it sounds insane, I have ran into one circumstance where the selection of the columns I retrieved affected the amount of time it took to complete the query.

    In my case, I narrowed down "the problem" to a LOB column that I didn't really need.

     

    MK

     

    SQL in Source:  SELECT * FROM EMP

     

    SQL when ALL columns are displayed:

    select
       "EMPNO", "ENAME", "JOB"
      ,"MGR", "HIREDATE", "SAL", "COMM", "DEPTNO"
      ,count(*) over () as apxws_row_cnt
    from (
      select * from (
        select * from emp
      ) r
    ) r
    where rownum <= to_number(:APXWS_MAX_ROW_CNT)

     

    SQL when only EMPNO and ENAME are displayed

    select "EMPNO", "ENAME"
        ,count(*) over () as apxws_row_cnt
    from (
      select * from (
        select * from emp
      ) r
    ) r
    where rownum <= to_number(:APXWS_MAX_ROW_CNT)
  • 4. Re: Interactive Report general Search is taking long time
    fac586 Guru
    Currently Being Moderated

    San wrote:

     

    Thanks for your reply. Mean time , i am not asking any query performance here. my query is taking 1 sconds to execute first 50 records.(I am Ok)

     

    The problem here is why the same query working differently in 2 IR.

     

    MY IR displaying the records quickly, while i am doing the search with IR , its not displaying .

     

    If you know just tell me , how IR search will work?

    For a "general search" using the Search Field in the IR Search Bar APEX will generate a query like this:

     

    select
        "CUSTOMER_NAME", "CUSTOMER_ADDRESS", "CUST_CITY", "CUST_STATE",
        "CUST_POSTAL_CODE", "TAGS", "CUSTOMER_ID", count(*) over () as apxws_row_cnt
    from
       (select
            *
        from
            (select
                customer_id, cust_last_name || ', ' || cust_first_name customer_name,
                CUST_STREET_ADDRESS1 || decode(CUST_STREET_ADDRESS2, null, null, ', ' || CUST_STREET_ADDRESS2) customer_address,
                cust_city, cust_state, cust_postal_code, tags
            from
                demo_customers ) r
            where
                ((   instr(upper("CUSTOMER_NAME"),upper(:apxws_search_string_1)) > 0
                  or instr(upper("CUSTOMER_ADDRESS"),upper(:APXWS_SEARCH_STRING_1)) > 0
                  or instr(upper("CUST_CITY"),upper(:APXWS_SEARCH_STRING_1)) > 0
                  or instr(upper("CUST_STATE"),upper(:APXWS_SEARCH_STRING_1)) > 0
                  or instr(upper("CUST_POSTAL_CODE"),upper(:APXWS_SEARCH_STRING_1)) > 0
                  or instr(upper("TAGS"),upper(:APXWS_SEARCH_STRING_1)) > 0 )) ) r
    where
        rownum <= to_number(:APXWS_MAX_ROW_CNT)

     

    This will clearly not make effective use of any relevant indexes on the tables, and requires obvious CPU overhead. If you want users to search efficiently then remove the Search Field from the Search Bar and only allow users to create explicit filters on columns. It may then be possible for the optimizer to push the resulting simple predicates down into the inlined report query to use indexes on the searched column:

     

    select
        "CUSTOMER_NAME", "CUSTOMER_ADDRESS", "CUST_CITY", "CUST_STATE",
        "CUST_POSTAL_CODE", "TAGS", "CUSTOMER_ID", count(*) over () as apxws_row_cnt
    from
        (select
            *
        from
            (select
                customer_id, cust_last_name || ', ' || cust_first_name customer_name,
                CUST_STREET_ADDRESS1 || decode(CUST_STREET_ADDRESS2, null, null, ', ' || CUST_STREET_ADDRESS2) customer_address,
                cust_city, cust_state, cust_postal_code, tags
            from
                demo_customers ) r
            where
                ("CUST_CITY" = :APXWS_EXPR_1) ) r
    where
        rownum <= to_number(:APXWS_MAX_ROW_CNT)
  • 5. Re: Interactive Report general Search is taking long time
    San Explorer
    Currently Being Moderated

    I agree with you Fac. But i am wondering how the same query working fine with my first report? If both reports are taking long time then its should be a problem.

     

    My first Report query

     

    SELECT
    a.*
    ,'Events' "Events"
    ,'Billing' "Billing"
    ,'Transaction Revenue' "TRX_Revenue"
    ,'Arrangement Revenue' "ARGMT_Revenue"
    ,'Lines' "Lines"
    ,'Invoice' "Invoice"
    ,'Holds' "Holds"
    ,apex_item.hidden (
      2,
      a.rs_trx_number) ||
    apex_item.checkbox (
      1,
      a.rs_trx_id,
      'onchange="spCheckChange(this, ''x01'', ''P13_TRX_AJAX_NAME'', ''P13_TRX_COLLECTION_NAME'',''SELECTED_TRX'');"',
      c.n001,
      ':' ) "Checkbox"
    FROM test a,
      apex_collections c
    WHERE a.rs_trx_id = c.n001(+)
      AND c.collection_name(+) = :P13_TRX_COLLECTION_NAME
    and a.org_id in (select distinct org_id from test1 where (role_name = :APPUSER_CURRENT_ROLE))

     

    My Second report Query

     

     

    SELECT a.*
      , 'Rev Lines' REV_LINES
      , 'Schedules' Schedules
      , 'Events'  Events
      , apex_item.hidden (
         2,
         a.rs_trx_number) ||
        apex_item.checkbox (
         1,
         a.rs_trx_id,
         'onchange="spCheckChange(this, ''x01'', ''P5_REV_TRX_AJAX_NAME'', ''P5_RTCN'',''SELECTED_REV_TRX'');"',
         c.n001,
         ':' ) "Checkbox"
      FROM test a,
      apex_collections c
    WHERE a.rs_trx_id = c.n001(+)
      and C.COLLECTION_NAME(+) = :P5_RTCN
    and a.org_id in (select distinct org_id from test1 where (role_name = :APPUSER_CURRENT_ROLE))

     

    Now my second report query is giving this problem.

     

    Cheers,

    San

  • 6. Re: Interactive Report general Search is taking long time
    Mike Kutz Expert
    Currently Being Moderated

    "Those are not the SQL statements you are looking for"....

     

    You need to run the page with DEBUG turned on, then look at the Debug for the SQL statement.

    The SQL statement that you see in the DEBUG is the real SQL statement that is being sent to the database, not what you typed in for 'Source'.

     

    I bet they are different.

    (Personally, I suspect that 'test' is actually a view..)

     

    MK

  • 7. Re: Interactive Report general Search is taking long time
    San Explorer
    Currently Being Moderated

    Hi Mike,

     

    You are right test is the View

     

    After i enabled the debug i found the below query

     

    Report 1 Query

     

    SELECT "Checkbox",
      "RS_TRX_NUMBER",
      "EXT_TRX_NUMBER",
      "ARRANGEMENT_NUMBER",
      "TRX_DATE",
      "TRX_STATUS",
      "SOLD_TO_CUSTOMER_NAME",
      "TRX_TYPE",
      "CURRENCY",
      "COMMITTED_AMOUNT",
      "BUDGET_AMOUNT",
      "TRANSACTION_BUDGET",
      "TRX_DESCRIPTION",
      "FILES",
      "Lines",
      "Events",
      "TRX_Revenue",
      "ARGMT_Revenue",
      "Holds",
      "RS_TRX_ID",
      COUNT(DISTINCT "ARRANGEMENT_NUMBER") over (),
      COUNT(*) over () AS apxws_row_cnt
    FROM
      (SELECT *
      FROM
        (SELECT a.* ,
          'Events' "Events" ,
          'Billing' "Billing" ,
          'Transaction Revenue' "TRX_Revenue" ,
          'Arrangement Revenue' "ARGMT_Revenue" ,
          'Lines' "Lines" ,
          'Invoice' "Invoice" ,
          'Holds' "Holds" ,
          apex_item.hidden ( 2, a.rs_trx_number)
          || apex_item.checkbox ( 1, a.rs_trx_id, 'onchange="spCheckChange(this, ''x01'', ''P13_TRX_AJAX_NAME'', ''P13_TRX_COLLECTION_NAME'',''SELECTED_TRX'');"', c.n001, ':' ) "Checkbox"
        FROM test a,
          apex_collections c
        WHERE a.rs_trx_id        = c.n001(+)
        AND c.collection_name(+) = :P13_TRX_COLLECTION_NAME
        AND a.org_id            IN
          (SELECT DISTINCT org_id
          FROM test1
          WHERE (role_name = :APPUSER_CURRENT_ROLE)
          )
        ) r
      ) r
    where rownum <= TO_NUMBER(:APXWS_MAX_ROW_CNT)

     

    Second Report

     

    SELECT "Checkbox",
      "EXT_TRX_NUMBER",
      "RS_TRX_NUMBER",
      "ARRANGEMENT_NUMBER",
      "TRX_RM_STATUS",
      "SOLD_TO_CUSTOMER_NAME",
      "TRX_TYPE",
      "TRX_DESCRIPTION",
      "CURRENCY",
      "COMMITTED_AMOUNT",
      "UNEARNED",
      "EARNED",
      "REV_TRANSFERRED",
      "REV_LINES",
      "SCHEDULES",
      "EVENTS",
      "EXT_TRX_ID",
      "PRIMARY_SALESREP_NUMBER",
      "RS_TRX_ID",
      COUNT(*) over () AS apxws_row_cnt
    FROM
      (SELECT *
      FROM
        (SELECT a.* ,
          'Rev Lines' REV_LINES ,
          'Schedules' Schedules ,
          'Events' Events ,
          apex_item.hidden ( 2, a.rs_trx_number)
          || apex_item.checkbox ( 1, a.rs_trx_id, 'onchange="spCheckChange(this, ''x01'', ''P5_REV_TRX_AJAX_NAME'', ''P5_RTCN'',''SELECTED_REV_TRX'');"', c.n001, ':' ) "Checkbox"
        FROM Test a,
          apex_collections c
        WHERE a.rs_trx_id        = c.n001(+)
        AND C.COLLECTION_NAME(+) = :P5_RTCN
        AND a.org_id            IN
          (SELECT DISTINCT org_id
          FROM Test1
          WHERE (role_name = :APPUSER_CURRENT_ROLE)
          )
        ) r
      ) r
    WHERE rownum <= to_number(:APXWS_MAX_ROW_CNT)
    ORDER BY "ARRANGEMENT_NUMBER" DESC,
      "EXT_TRX_NUMBER"

     

    In Source i am using the same query in both report , But how come order by clause has been adding in my Second query.

     

    Also in Report 1 :

      COUNT(DISTINCT "ARRANGEMENT_NUMBER") over (),

      COUNT(*) over () AS apxws_row_cnt

     

    The same is not there in Second report query .

     

    How can i remove that order by clause from my id also how can i resolve the above change (

      COUNT(DISTINCT "ARRANGEMENT_NUMBER") over (),

      COUNT(*) over () AS apxws_row_cnt)

     

    Help me out to resolve.

     

    Cheers,

    San

  • 8. Re: Interactive Report general Search is taking long time
    Mike Kutz Expert
    Currently Being Moderated

    count(*) over () as apxws_row_cnt

    I've used this trick before in other non-APEX applications.

    It allows me to get the total count of returned rows from parsing just the first record in the result set.

    Which allows me to print out "N records found" and/or "you are on page n out of M"

     

    ORDER BY...

    These are added to the SQL when you sort by column(s) within the IR.

    To get rid of them, you need to NOT SORT the IR... which end-users will hate.

    You're better off making sure the underlying table(s) have the appropriate Indexes on them

     

    Run this in SQL Developer and compare it to one without the ORDER BY clause:

    select * from test order by arrangement_number desc, ext_tx_number

     

    COUNT(DISTINCT "ARRANGEMENT_NUMBER") over ()

    This is possibly due to an aggregate or compute column defined within the IR.

    I haven't messed with those too much.

     

    MK

  • 9. Re: Interactive Report general Search is taking long time
    San Explorer
    Currently Being Moderated

    Thanks Mike, You are right. Index missing, View query doing Full table Scan, Have added the index , now report working fine.

     

    Cheers,

    San

Legend

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