Improving Performance in ADF Applications - Page Load Time in Query Forms

I have noticed, in documentation or sample applications, very basic and at the same time important problem usually is not discussed - initial Web page load time. When developer creates Web page with ADF framework, most often implementation is done in default way - on page load database is queried and row data is shown to the user. However, such default approach is very dangerous in large applications. In this post I will focus on Query type forms, CRUD type forms will describe in next post.

Yes, there are options and you need to tune ADF BC components - Advanced View Object Techniques. However, its not enough - may be someone will not agree, but only ADF BC tuning will not help you too much with application performance. Significant improvement in performance can be achieved with clever functionality design in your ADF application. In this post, I will provide one suggestion for Query type forms.

Query type forms usually contain Query area and Results area. In ADF 10g, we were hiding results on initial page load, however query still was executed in the background. In ADF 11g, this case is significantly improved and we can prevent initial query invocation - means eleminate not needed select to database. Logically thinking - without database query we will get faster page load, and its true.

Let's check a default case, when page is loaded and data is shown. In this case, on page load, ADF framework will execute SQL queries for all databound components available on the page:


SQL queries will be executed even for LOV components. This means, if your page contains 1 results table and 10 LOV's, database will receive 10 + 1 = 11 SELECT statements. Since its initial page load, there will be full SELECT for results table. Yes..., it can take some ~15 minutes just to load and present page to the user, if there are at least some 100000 rows in results table and 1000 - 5000 rows in each LOV table. And its not related to ADF BC tuning at all. Who needs a system, where page will be loaded 15 minutes? Answer is simple - no one :-)

I'm happy, in ADF 11g there is standard solution for this problem. You need to use Query Criteria in your Query type forms. Query Criteria allows you to declaratively specify not to query dataset automatically:


With such configuration, on page load ADF framework will not send any SQL queries to database and page will be rendered in blank mode:


This means, page will be rendered immediately even if there are lots of records in database.

And only when user will press Search button, SELECT statement will be sent to database to query required data:

[1051] EmployeesView1 ViewRowSetImpl.doSetWhereClause(-1, vc_temp_1, A)
[1052] ViewObject: EmployeesView1 close prepared statements...
[1053] ViewObject: EmployeesView1 Created new QUERY statement
[1054] EmployeesView1>#q computed SQLStmtBufLen: 555, actual=457, storing=487
[1055] SELECT Employees.EMPLOYEE_ID,
Employees.FIRST_NAME,
Employees.LAST_NAME,
Employees.EMAIL,
Employees.PHONE_NUMBER,
Employees.HIRE_DATE,
Employees.JOB_ID,
Employees.SALARY,
Employees.COMMISSION_PCT,
Employees.MANAGER_ID,
Employees.DEPARTMENT_ID FROM EMPLOYEES Employees
WHERE ( ( ( ( UPPER(Employees.FIRST_NAME)
LIKE UPPER( :vc_temp_1 || '%') )
OR ( :vc_temp_1 IS NULL ) ) ) )
[1056] Bind params for ViewObject: EmployeesView1
[1057] Binding param "vc_temp_1": A


LOV queries will be executed at that time as well (only once):

[1160] SELECT Departments.DEPARTMENT_ID,
Departments.DEPARTMENT_NAME,
Departments.MANAGER_ID,
Departments.LOCATION_ID
FROM DEPARTMENTS Departments


Download sample application - ADFPerformance.zip

Spanish Summary:

En este post, se muestra unos tips para mejorar la ejecución de las consultas usando Jdeveloper 11g. Para lograr este propósito Andrejus nos muestra de que manera podemos usar a los Query Criteria para inicializar la consulta de datos con valores vacios y evitar recargar los formularios de datos.