VO Fetch Configuration

View Object Performance primarily depends on how it is fetching the data from the data base.
'Row Fetch limit' in adf-config limits the number of records to be retrieved from the data base.
Row Fetch limit is an application level setting and it applied to all the view objects in the application.
One can override this value for a particular VO by setting MaxFetchSize at the view object level.

Following are the two test cases with a row limit set to 500 while querying a table with 1000 records

Test Case1:
------------

Total Rows in table: 1000
Row Fetch Limit: 500 rows
Max Fetch Size = -1

<ViewObject
  xmlns="http://xmlns.oracle.com/bc4j"
  Name="VOConfigTestVO"
  Version="11.1.2.62.94"
  SelectList="VOConfigTestEO.NUM"
  FromList="FUSION.VO_TEST VOConfigTestEO"
  BindingStyle="OracleName"
  CustomQuery="false"
  PageIterMode="Full"
  UseGlueCode="false"
  FetchMode="FETCH_AS_NEEDED"
  FetchSize="1000">
 

In this testcase, while retrieving the records, the max fetch size is set to -1 (Default Value). But on the page, while retrieving, it is observed that there is an error while scrolling for records after 500.



Test Case2:
------------
Total Rows in table: 1000
Row Fetch Limit: 500 rows
Max Fetch Size = 900

<ViewObject
  xmlns="http://xmlns.oracle.com/bc4j"
  Name="VOConfigTestVO"
  Version="11.1.2.62.94"
  SelectList="VOConfigTestEO.NUM"
  FromList="FUSION.VO_TEST VOConfigTestEO"
  BindingStyle="OracleName"
  CustomQuery="false"
  PageIterMode="Full"
  UseGlueCode="false"
  FetchMode="FETCH_AS_NEEDED"
  FetchSize="1000"
  MaxFetchSize="900"> 

In this test case, we have overridden the row limit to 900 using MaFetchSize, after which we were able to retrieve 900 records from the database.

FetchSize:

In both these testcases, FetchSize attribute is set to 1000, which means we would retrieve 1000 records from DB in one trip. By Default the value is set to 1, which was taking long time while scrolling the records.

RowCount Threshold:
While displaying the table, to accurately size the scroll bar for the table, application would actually execute a select count query on the View object to estimate the number of records. This select count query can be a costly query with an increase in user wait time ans also it has a negative impact on memory. One can control this behavior using a property called RowCountThreshold on iterator binding.

In the Test Case1, RowCountThreshold is set to 0 and following query is seen in the log:

<ViewObjectImpl> <buildQuery> [401] SELECT VOConfigTestEO.NUM FROM FUSION.VO_TEST VOConfigTestEO WHERE ROWNUM <= 501
<ViewObjectImpl> <getCappedQueryHitCount> [402] ViewObject: [model.view.VOConfigTestVO]VOConfigTestAM.VOConfigTestVO1 Capped Row Count Query Statement:
<ViewObjectImpl> <getCappedQueryHitCount> [403] "SELECT count(1) FROM (SELECT VOConfigTestEO.NUM FROM FUSION.VO_TEST VOConfigTestEO WHERE ROWNUM <= 501) "
<ViewObjectImpl> <getCappedQueryHitCount> [404] Bind params for ViewObject.getCappedQueryHitCount: VOConfigTestVO1
<ADFLogger> <addContextData> Estimated row count
<ViewObjectImpl> <getCappedQueryHitCount> [405] ViewObject: [model.view.VOConfigTestVO]VOConfigTestAM.VOConfigTestVO1 Capped Row Count (501): 501

When we set this RowCountThreshold to -1, Capped Row Count Query wont be fired, to size the scrollbar. So with this option, the scroll bar size wont be fixed on the load of the page and it  would become smaller as more rows are retrieved.

Also i found  this nice article on the VO tuning part written by Vinod Krishnan.