Sunday, August 30, 2009

LOV execute query many times (Fixed in 11.1.1.3)

Input list of values component has big performance issues, and it seems not suitable for view objects with a lot of data.
Not only you cant use performance tuning since there are bugs if you use range paging:
http://adfbugs.blogspot.com/2009/07/performance-tuning-lovs-and-range.html
and Up to row number:
http://adfbugs.blogspot.com/2009/08/lov-view-object-tuning-only-up-to-row.html

but also because every time you open LOV and select a value it execute query many times.

To test that, i override the method executeQuery for collection and print the query:

@Override
protected void executeQueryForCollection(Object object, Object[] object2, int i) {
System.out.println("EmployeesLOV executes Query :" + this.getQuery());
super.executeQueryForCollection(object, object2, i);
}

when i open the lov and select a value i get the following log:

EmployeesLOV executes Query :Select * from Employees
30-Aug-2009 17:25:04 oracle.adfinternal.view.faces.renderkit.rich.RichRenderKit isNavigating
SEVERE: The original view root was not set.
EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( ( (EMPLOYEE_ID = :vc_temp_2 ) ) ) )
EmployeesLOV executes Query :Select * from Employees


so the LOV query is executed 3 times !?

If i type a value to fields that many LOV values start with this value then it is executed many times !!!.

EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( (EMPLOYEE_ID LIKE :vc_temp_2 ) ) )
30-Aug-2009 17:31:34 oracle.adfinternal.view.faces.renderkit.rich.RichRenderKit isNavigating
SEVERE: The original view root was not set.
EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( (EMPLOYEE_ID LIKE :vc_temp_2 ) ) )
EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( (EMPLOYEE_ID LIKE :vc_temp_2 ) ) )
EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( (EMPLOYEE_ID LIKE :vc_temp_2 ) ) )
EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( (EMPLOYEE_ID LIKE :vc_temp_2 ) ) )
EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( (EMPLOYEE_ID = :vc_temp_2 ) ) )
EmployeesLOV executes Query :Select * from Employees


Also while you scroll up and down in LOV the query is also executed many times.

Finaly if you type a value that many LOV values start with this value and scroll down then other values that dont start with this value are visible:



And if you select the value then you get a strange error:



Is this a bug?

Is there a way to use LOVs with a lot of data?

Test case:
http://adfbugs.googlecode.com/files/TestPerformance.zip

For the test case i put in Employees table about 100000 rows with the following procedure:

create or replace
PROCEDURE CREATE_EMPLOYEES AS
BEGIN
FOR i IN 1..100000 LOOP
Insert into employees (employee_id,
last_name,
email,
hire_date,
job_id)
select employees_seq.nextval as employee_id,
'auto' i as last_name,
'aut' i '@gmail.com' as email,
sysdate as hire_date,
'IT_PROG' as job_id
from dual;
END LOOP;
COMMIT;
END CREATE_EMPLOYEES;

6 comments:

  1. Bug No: 8983396 EXTRA QUERIES EXECUTED FOR LOV AND PERFORMANCE ISSUES is logged

    ReplyDelete
  2. With patch 1 it is improved but not fixed.
    When you scroll down LOV and when you select a row in lov query is not executed. But when you return a value from LOV Select * from Employees
    is executed, whitch is not needed, since you have allready selected a specific value.
    Also when you type specific value in lov Select * from Employees is executed again.

    ReplyDelete
  3. Hi Michael

    You say this is fixed in 11.1.1.3.0 but I still get this, i.e.: LOV query for input text with LOV field executing multiple times, once when initially searched and then number times when lov is closed to return values to input text field. Any suggestions on how this can be stopped or tuned further?

    Regards,
    Mario

    ReplyDelete
  4. In the attached test case the results in 11.1.1.3 are:
    When i open LOV 1 query:
    EmployeesLOV executes Query :Select * from Employees

    when i select a row and press OK, 1 query:
    EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( ( (EMPLOYEE_ID = :vc_temp_1 ) ) ) )
    This is probably the validation of the field.

    When i manualy type a value in LOV field, 2 queries:
    EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( ( (EMPLOYEE_ID LIKE ( :vc_temp_1 || '%') ) ) ) )
    EmployeesLOV executes Query :SELECT * FROM (Select * from Employees) QRSLT WHERE ( ( ( (EMPLOYEE_ID = :vc_temp_1 ) ) ) )

    This seems like the minimal queries that needs to be excecuted.

    Do you have a different behaviour?

    Other tuning options we use:
    >>Range paging in LOV, more queries but faster excecution. It still has some bugs
    >> FIRST_ROWS hint on tuning.
    >> Fixing query (no Distinct clause, order by according to indexes)

    I hope this is helpfull

    ReplyDelete
  5. I am occupying "executeQueryForCollection" and raise a popup to select the first record search, and "executeQueryForCollection" gives 2 turns in the first round is all good, but the second round brings all null values​​, I can do?

    Marcelo from CHILE.

    ReplyDelete