Forum Stats

  • 3,874,184 Users
  • 2,266,680 Discussions
  • 7,911,761 Comments

Discussions

Interactive Grid takes long time to load

gkthomas
gkthomas Member Posts: 437 Bronze Badge
edited Nov 9, 2022 5:42PM in APEX Discussions

I am using Oracle apex 20.2 on Chrome

I am have an Interactive Grid with following query . Pulling data from a single table. The orginal query is only following

 select ,,.,, from  MY_BALANCE_DETAILS

  where  1=1

  AND HEADER_ID = :P5_HEADER_ID 

*****************************************************************************

The table has 347870 total rows . With the original query When I run the Grid for a particular Header_id it takes 3-4 seconds maximum.

Now my users wanted 2 additional filters( the reason is cascading lov columns dont have filters) . So I added 2 drown down popup lovs above the grid which has null value when page load. So users can select the filters and press a button to apply filters . This part working fine.

However on page load, The filters are null , so I want to load all data for that particular Header_id selected. So I added two lines in the where clause like following . But when I added this lines, the Grid takes 30 seconds to load (page load with filter has null value) . The same query( modified with filters) in Toad takes only 200 ms. Am I doing anything wrong. What I need to improve the performance of the Grid.

 select ,,.,, from MY_BALANCE_DETAILS

  where  1=1

  AND HEADER_ID = :P5_HEADER_ID

AND PROJECT_ID      = nvl(:P5_PROJECT_LIST_F, project_id)

  AND ORGANIZATION_ID     = nvl(:P5_ORG_LIST_F, ORGANIZATION_ID)

Please help

I am using Oracle apex 20.2 on Chrome

George

Answers

  • Hamza Al-abbasi
    Hamza Al-abbasi Member Posts: 784 Bronze Crown

    Hi gkthomas,

    Please always use code formatter for your text code.

     where  1=1

    What is the purpose for this condition ?

    The table has 347870 total rows . 

    • Use Pagination Type = Page, from IG Attributes => Pagination Type.
    • Set Lazy Loading = On, from IG Attributes => Performance.


  • gkthomas
    gkthomas Member Posts: 437 Bronze Badge

    Thanks for your response .

    Using "where 1=1 " simply because it is easy to comment following lines as needed. removing "where 1=1 "does not make any difference . all the the above settings were already done.

    if I remove following two lines, time would come back to 3-4 seonds. But with those lines its 30 seconds.

    AND PROJECT_ID      = nvl(:P5_PROJECT_LIST_F, project_id)

    AND ORGANIZATION_ID     = nvl(:P5_ORG_LIST_F, ORGANIZATION_ID)

    George

  • InoL
    InoL Member Posts: 10,198 Blue Diamond

    Run the page in debug mode level 9 and take a look at the execution plans for these two situations. They are probably something completely different.

    From there you can start to optimize your query.

  • gkthomas
    gkthomas Member Posts: 437 Bronze Badge

    This interactive is using for data entry also. So there are Lovs on Both Project ID and Organization ID in the grid. If I change the Project id Grind column and org id column from Lov to Text , the Grid loading fast. So it is basically the the Lovs and above where clauses together causing the problem . Is there any method I can use

    so that these two lines execute only when there is value in the dropdown P5_PROJECT_LIST_F and P5_ORG_LIST_F

    AND PROJECT_ID      = nvl(:P5_PROJECT_LIST_F, project_id)

    AND ORGANIZATION_ID     = nvl(:P5_ORG_LIST_F, ORGANIZATION_ID)

  • InoL
    InoL Member Posts: 10,198 Blue Diamond

    You have to use the usual tools to optimize queries. Start with the query that actually runs when APEX adds the LOV lookups to your query. For this, run your page in level 9 debug.

    Another option it to use "Function returning query" as the source, so you can add the lines dynamically:

    v_query := 'select ....';
    if :P5_PROJECT_LIST_F is not null then
      v_query := v_query || ' AND PROJECT_ID = nvl(' || :P5_PROJECT_LIST_F ||', project_id);
    end if;
    return v_query;
    

    (syntax is not really correct, but you hopefully catch the idea).