Restricting the number of items Select One Choice (Choice List) LOV
By skatyal on Feb 11, 2013
While implementing an LOV, the choice has to be made about the UI component that will be used for displaying the list of values. When this list is small the preference is given to the Select One Choice (choice list). Otherwise Combo Box is preferred because it provides additional functionality like search, filter etc. Select One Choice executes database query on page load to retrieve the list data whereas the LOV queries are not executed at page load. These are executed only on demand, when the user opens the LOV component. This implies that for faster page loading, we should use Select One Choice only for those attributes for which the returned list contains few rows. On second load, however, the values for the Select One Choice will be retrieved from the cache. Thus, there might be inconsistencies if the data is changed. So, if there is a long LOV the Select One Choice might not be the best choice.
In the UI Hint tab, if the component selected is Combo Box then the UI provides the facility to add the Search/Filter facility; it allows to specify the MRU count, query limit and other options. The Query Limit serves to control the VO fetch size and to change the number of records the Combo Box can display. Specifying a large value is not recommended. When the Query Limit is set, then the VO’s source XML file is modified internally. The <ListBinding> tag created for the LOV is changed and the attribute ListRangeSize is set to the value specified for the Query Limit. The <ListBinding> element maps the list data source attribute to the LOV-enabled attribute. The ListRangeSize attribute governs the number of items that will be shown at time in the component.
The number of rows that the LOV fetches is determined in part by the ListRangeSize setting in the LOV-enabled attribute’s list binding definition. This definition can be specified in the Edit List of Values dialog (of this attribute, in the VO Overview editor). If the number of records fetched is very large, the default value for ListRangeSize may truncate the values (to be displayed in the component). The default number of fetched records for LOV depends on the type of component used to display the records. For instance, in the case of the Combo Box with List of Values component and the Input Text with List of Values component, the default value for ListRangeSize is 10. For all other types of list components including Select One Choice list, the default value for ListRangeSize is set to -1, meaning all. However, the ListRangeSize value has no effect on the records that the end user can search on in the lookup dialog displayed for the List of Values type components.
Query Limit is disabled for all other component types including the Select One Choice List component. These components place no restriction on the number of rows that the LOV will access. However, it is not recommended to set ListRangeSize=-1 since it may lead to a performance hit. For components like Select One Choice list this value can be modified manually in the VO source XML file and set to a smaller size.
Apart from setting the above properties, it is required to set some tuning options for improving the performance of the LOVs. The View Accessor is used to access the data source for LOV attribute. It is an ADF BC mechanism that lets one obtain full list of possible values from the row set of the data source VO. The View Accessor can also be used to limit the set of values the LOV displays. The View Accessor Edit dialog provides the facility to set the tuning options.
The count specified for In Batches of specifies the number of rows to be fetched from the database in each batch. The As Needed and All at Once options work in conjunction with the value of the In Batches of for determining the number of round trips. Thus the in Batches of specifies the fetch size. When the access mode is set to Range Paging, Range Paging Auto Post, or Range Paging Incremental, the range size is used to specify the number of rows to be fetched for the row set. The default range size is -1 and fetches all the rows.
Thus, to conclude, the fetch size and range size specify the number of rows fetched from the database and the number of round trips to the database. They do not control the number of LOV items displayed at runtime. This is controlled by the Query Limit attribute on the UI Hint Tab for the Combo box list of values LOV component and the ListRangeSize attribute of the < ListBinding> tag in the VO xml for all other LOV components.