Sunday, January 9, 2011

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.

7 comments:

Thiru said...

This is a very useful info. But I got a question here. You have mentioned

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

But I have noticed if we have the Combobox Lov View Accessor dependent on any other parent VO attribute via bind variable and if the dependent attribute value changes then the view accessor is executed irrespective if the LOV is clicked or not.

Eg in a page I have text box - dept id and 10 Lov's and all view accessors has a bind variable mapped to view attribute dept id. If I put some value to dept id then automatically all the view acessors are executed.

Andrejus Baranovskis said...

Yes, its seems like different Use Case. You have cascading LOV's.

Andrejus

Saurav Kumar said...

Hi, this is really a nice topic, I have implemented the same combo box with list of values, but When I select any item from the list it shows the internal code instead of showing the UI Hints on the input box, could you guide me if I am missing something?

Andrejus Baranovskis said...

Hi, I'm not sure if I understood you exactly, but may be this post will help - http://andrejusb.blogspot.com/2009/11/defining-lov-on-reference-attribute-in.html

Andrejus

priyanshu said...

Thanks,
Actually, I have one view accessor(territoryPVO) in my VO and I wanted to show TerritoryShortName in the dropdown list and wanted to save IsoTerritoryCode in the database, So in the configuration tab of 'edit list of values dialog' I have selected data-source as my view accessor(territoryPVO) and listAttribute as IsoTerritoryCode.
and in the UI Hints tab I have selected the TerritoryShortName.(which is available in the view accessor attributes)

now when I run the application it shows the TerritoryShortName in the dropdown list which is fine, but when I select any value from the list, it shows IsoTerritoryCode in the input box of the dropdown list.

Deeepa Pathre said...

I have got one question I am trying combobox with list of values of drop down doesn't get expand according to conectent it is just stuck to width of combo, can anybody suggest solution?
Thanks
Deepa

Anonymous said...

Hi... I have a selectonechoice component for a dropdown. The values in the dropdown are getting populated from a database table.Based on that value am filtering some records.If a valid data is given and on clicking search button it returns appropriate records.. But when a mismatch search citeria is given and on clicking search it navigates to no data to display... but when i click search again... in the selectonechoice fields only select is visible and the other values in the dropdown getting vanished.... Kindly help on this... Trying hard to figure it out... Thanks in Advance