Comparing ADF View Object and Row Fetch Execution Times

I bet one of the most common doubts ADF developer, or may be DB admin, has - why VO SQL executes slower than the one identical from SQL Plus? This is often the case, but there is nothing VO SQL to blame about. Usually VO SQL executes in almost same time, as it would execute in SQL Plus - but there is extra added time of row fetch. When ADF UI page is rendering, data becomes available after VO is executed and rows are fetched from result. These two separate technical actions, look like one for ADF UI user - data will not be displayed, until it is not fetched. So, there is extra time for row fetch on top of VO SQL query execution. This is why - displaying data in ADF UI, could be slower than displaying it in SQL Plus.

You should tune number of rows fetched by VO's, the worst practice is to set Range Size = -1 and fetch all rows at once. This is going to consume significant amount of memory to create many ViewRowImpl objects and waste time. Make sure to fetch only such amount of rows, close to be required for display on UI or any back-end operation.

I'm going to show you with a practical test - how time differs between VO SQL execution and row fetch for simple Employees table with 107 rows fetched as total. Here you can download sample test case application - ADFFetchApp.zip.

There is one iterator in Page Definition, for Employees VO:

I have set this iterator with Range Size = -1, to fetch all rows. I have seen the use cases, while reviewing and tuning ADF applications, where developers are using Range Size = -1 without actually understanding what a side effect for runtime performance it could create. Afterwards, it is common to blame ADF for a mystery, well hard to blame. Anyway, here is my test case Range Size = -1 setting for Employees iterator:

Sample application comes with a generic VO implementation class. There are two methods overridden here - executeQueryForCollection and createRowFromResultSet. In the first method, I'm tracking how long it takes to execute SQL for given VO. Second method tracks total time to fetch all available rows from Employees table (remember Range Size = 1 from above):

Fetch time is initialised, before constructing first ViewRowImpl object and stopped with the last row.

Just run sample application, after test page with Employees rows will be rendered you could check execution times and compare them:

In average it runs as following: 15 milliseconds for VO SQL query execution vs. 94 milliseconds for total row fetch:

As you can see, time added by row fetch operation could be quite significant and it makes sense to tune and ensure optimal row fetch in your application. SQL query is not only one thing to blame, when VO performance is slow, it could be you are fetching too many rows.