Troubleshooting ADF ViewObject Range Paging Issues

ADF BC ViewObjects provide a very valuable mechanism to page through large data sets so that a user can navigate to a specific page in the results. Range Paging fetches and caches only the current page of rows in the ViewObject row cache (at the cost of another query execution) to retrieve each page of data. Range paging is very performing when your ViewObject has access to (hundreds of) thousands of database rows, and if you want to avoid JVM memory overconsumption. In my current project I ran into two severe performance problems that I will discuss in this blog. 

Issue one: Range Paging generates a SQL hint that can be very inefficient

The main search screen of our application uses ViewObject Range Paging. Extensive tuning and testing by our database experts proved that the query was extremely performing in the database when the concept of range paging was used (we only query around 40 – 50 rows each time). On our development environment everything worked fine. We always do performance analysis and regression analysis after new patches/releases to our test/acceptation environment. To our surprise, after a new release we detected a very slow ViewObject query and a very slow ‘SELECT COUNT(1)’ query in the ADF Performance Monitor, that was generated by our ViewObject with Range Paging. These queries correspond to the ViewObject methods executeQueryForCollection and getQueryHitCount():

After analyzing, we noticed that the ADF framework adds a hint to the ViewObject SQL:  /* FIRST_ROWS */

and this is exactly the problem in our case. First of all, the hint performs better when the range size (50) would be added to the SQL that we configured on our ViewObject:    /* FIRST_ROWS (50) */

But in our case (to my own surprise) without the added SQL hint, the query is much faster: 100 milliseconds instead of 20 seconds! Our database expert explained that this was due to the execution plan that was chosen by the database. We searched and found a way to prune the SQL hint out of our query. The regex pattern for an SQL hint can be:

private Pattern pattern = Pattern.compile("/\\*\\+.*?\\*/", Pattern.DOTALL);

and by overriding the ViewObject method buildQuery() we can replace the hint with an empty string:

In this way the query performs really well – under 100 milliseconds.

Issue two: the SELECT COUNT(1) can be very slow

Frequently a ‘SELECT COUNT(1)’ query is executed to manage the number of rows displayed in a table, tree or treetable. When ViewObject Range paging is used, the framework automatically executes this query because it needs this information to manage the user scrolling up and down the table. In some cases this query can be very expensive and troublesome. In some case a RowCountThreshold at the iterator can be useful to avoid a full table scan:

But in our case we had to create a dedicated ViewObject with a different, more efficient query to count the number of selected rows. We created a method on the ApplicationModule that executes this ‘CounterViewObject’. Now, the original ViewObject calls this method instead of the standard ‘SELECT COUNT(1)’ query. We do this in the overridden method getQueryHitCount():

And in the counter method on the ApplicationModule the ‘CounterViewObject’ is executed:

In this way we could solve our performance issues with ViewObject Range Paging; now we have a very performing search screen with Range Paging:

More information on the ADF Performance Monitor

You can get more information on the ADF Performance Monitor website at adfpm.com. A one month trial version is available on demand here.