a ADF Table performance on large records

060317_2152_ADFTableper1 ADF Table performance on large records

I had a requirement to display a table in a page which contains more than 40,000 records. Initially, I have added this as an ADF table with Pagination and assumed it cause no harm to the user. Everything works fine except it takes 3 minutes to load the simple table in the browser. 

I investigated my page why it took 3 minutes to display a simple table even I applied the performance tuning settings in my view object. The below-tuning parameters did not help me, and my page is not given up to me.


060317_2152_ADFTableper2 ADF Table performance on large records


To investigate further, I turned on the FMW JBO to FINEST (ADF Debugging) which will display SQL statements in the log files. I found two things from the debug log regarding the performance

1. At first, ADF performs a SELECT ALL query which loads all the records from the table and saves it in the Java RESULT SET (2 minutes)

2. The second time, It performs select COUNT (*) from the table for pagination (another 1 minute)

So the ADF page initially loads all the records and save it in the Java result set, so the subsequent pagination request goes to the result set object and fetches the corresponding records. It leads two issues to me, the SQL Query time and the result set object saved in memory (40k records L ). The memory will go out from the machine if there are multiple people access the same page.

We have designed a custom solution to this issue that I have described in this section. The summary of the custom approach is below

1. First, display the ADF table with no records or very less record

2 Second, Display the table based on the user search criteria by overriding the query listener


You can download the ADF Table Performance project at the end of this page. 


ADF Table performance – Custom solution example

1. I have used Employee table in HR schema for this example. As you see below, I dropped the Employee View object as an ADF table in the JSF page. Also, I added BeforePhase listener for the af:view tag. The method in the BeforePhase will execute before the page loads.



060317_2152_ADFTableper3 ADF Table performance on large records


2. This is my beforePageLoad method which executes a where clause that returns no record (0=1), so the ADF table displays 0 records  




3. As you see below, I override the queryListener in a backing bean method. The ADF table will display the records based on the user search query


060317_2152_ADFTableper4 ADF Table performance on large records


4. Below is the Query Listener method, where I do a custom search based on the user search criteria



Helper Method that gives Iterator Binding Object 




5. This is the initial default page that loads no records.


060317_2152_ADFTableper5 ADF Table performance on large records


6. I input in FirstName and hit Enter


060317_2152_ADFTableper6 ADF Table performance on large records


7. Another search:


060317_2152_ADFTableper7 ADF Table performance on large records


Download the Project: ADF_TABLE_PERFORMANCE



Note: In case, you are using JSFF page instead of JSF page then beforePhase listener won’t work. Implement the Java interface oracle.adf.model.RegionController in JSFF backing bean class and put the before page load code into the overridden method refreshRegion() and register the backing bean class as a ControllerClass in the Page Definition  <pageDefinition>  file. 


For Example: 



govindan ADF Table performance on large records
Connect me


Thank you for visiting my personal blog. Myself Govindan, Software Developer by profession since 2006 and hence I started this blog early in 2016 and ever since I've been writing about technologies experienced and learnings of everyday life.

The views expressed on this blog are my personal views and do not necessarily reflect the views of my employer.

Please feeling free to reach me on any comments and feedbacks you have. Would be more than glad to listen and reply 🙂
govindan ADF Table performance on large records
Connect me
By | 2017-06-05T22:02:36+00:00 June 3rd, 2017|Categories: ADF|Tags: , , |Comments Off on ADF Table performance on large records
Like us on Facebook.