ADF 11g Performance Tuning - Select One Choice and Combo Box with List of Values

Blog reader was asking why ADF 11g page load time is slow, in case when page contains multiple heavy Select One Choice components. See comments for this post - Oracle ADF 11g Release 1 - Runtime Page Load Performance Improved !. Also, it seems like people are mixing two things together - Select One Choice and List of Values (LOV). Before JDeveloper 11g R1 (before Fusion Middleware was released), we were experiencing such issue, when List of Values query was executed, immediately on page load. Of course this was causing lots of delays, imagine to execute 20 additional SQL queries for 20 List of Values components available on the page. However, since JDeveloper 11g R1 this issue was fixed and List of Values queries are not executed anymore on page load, only on demand - when user opens LOV component. With Select One Choice is another story, this component must be populated on page load, it will execute database query to bring list data. This means for better page load time, you should use Select One Choice only for those attributes, where returned list contains only few rows. At the end, there is no point to have long Select One Choice list, it will be unusable anyway.

Based on sample application - ChoiceListLOVPerformance.zip, I will describe the difference in execution between Select One Choice and Combo Box with List of Values. All tests are done on local Oracle Enterprise Linux VM (4 GB RAM from total 8, 2 Processor Cores from total 4), all products - Oracle XE and JDeveloper 11g PS2 are installed on the same VM. Application implements Master-Detail relationship (based on standard HR schema - Departments/Employees), I will test how fast Detail screen can be opened, depending on Select One Choice or Combo Box with List of Values usage. Simple Master screen:

Detail screen contains table with two Select One Choice columns. As you can see from Firebug report, first load for this table is done in 5.17 seconds:

On page load, we can identify from the log, both Select One Choice components are generating database queries (as expected). This affects page load time, Jobs query:

Departments query:

If we reopen same screen, for the same Master record, lots of ADF scripts are cached already. Database queries for Select One Choice components are not executed from database anymore on second load, instead - information is retrieved from cache:

Load time for the same screen is 1.59 seconds now. I will move to another Master record:

Select Once Choice queries are not executed anymore, retrieved from cache - load time is quick, only 884 milliseconds:

Another Detail screen with more data is loaded in 1.55 seconds - Select One Choice data is retrieved from cache:

JobId attribute is defined now as Select One Choice:

Will change it to Combo Box with List of Values:

Will change DepartmendId as Combo Box with List of Values as well:

Both attributes are defined now as Combo Box with List of Values:

Initial load for Detail screen is done now in 3.77 seconds, this is much faster comparing to 5.17 seconds when we were using Select One Choice attributes and database queries were executed on page load:

Database query for Combo Box with List of Values will be executed on demand, when user will select new value:

Executed query, query results will be cached for future requests, this means it will not be re-executed:

For Combo Box with List of Values we can define View Criteria to filter retrieved Combo Box elements. If user wants to select other values, List of Values popup can be opened to perform search:

While Select One Choice is relatively light component, it requires database query execution on page load. You may consider using Combo Box with List of Values or just List of Values components for better page on load performance.