Friday, February 26, 2010

Limit Query Results based on custom property

In ADF, for performance reasons you need to limit the results of the query of a view object. Also you need to inform the user that the results are limited so that he will know that he need to put specific criteria to find specific records.

You can use up To Row Number property in view object tuning, yet this just limit the fetched records and not the query.

You can use Range Paging option. Yet data for each row must be posted before you change row, so that makes it hard to use on view objects used for transactions.
So if a view object is based on entity that has many data it make it hard to tune.

An option for better performance is to use performance tip on tunning FIRST_ROWS and at the same time to limit the results of the query adding in where clause ‘ROWNUM <= :queryLimit’. In order to do that generic and not to change where clause of queries for each view object you need to tune, I use a custom property on view object ‘QueryLimit’ In the ViewObjectImpl

I override method executeQueryForCollection and, if there is a 'QueryLimit' property on view object then it apply a limit to the query of view object "ROWNUM <=QueryLimit" before performing query. Also after query I set the property "ResultsLimited" to "true" if queried records are not less of the QueryLimit.

I also have a method that checks ResultsLimited and return a message that the results are limited. I expose that method to client and use it from backing bean to show message to the user when results are limited.
We have put the logic in our base ViewObjectImpl class so it can be used from every view object
This approach boosted the performance in our application, of transaction pages that was based on tables with large amount of data.
Test Case


  1. Great article. Lots of good statistical information found right there. Interesting to see how much all those big blogs are making.

    public records

  2. Hi

    Can u tell the schema of the HR Connection. It will be helpful if you specify the schema