ADF Autosuggest Performance Tuning and Testing

December 22nd, 2010 | Posted by in Oracle ADF | No Comments

In what follows we will study how to configure the ADF autosuggest component to optimize it for best performance.

Introduction

In order to implement the autosuggest functionality in ADF you can use one of the following blog entries:

http://www.gebs.ro/admin/blog/oracle/oracle-adf-form-autocomplete-using-autosuggest-behavior-search-view-add-edit/

http://www.gebs.ro/admin/blog/oracle/oracle-adf-autosuggest-behavior/

To get the list of suggested items and display it to the user when he/she types a char/string the af:autosuggest component uses a View Criteria which performs a query into the database table.

Oracle ADF Performance Tuning Official Documentation states that in order to speed up database processing, a database index should be created on the attribute for which autosuggest behavior is enabled.

In what follows we shall analyze the above statement using Oracle XE dbms.

Prerequisites

First we shall alter the HR schema with a new table:

CREATE TABLE "HR"."PEOPLE"
  (
    "ID"   NUMBER NOT NULL ENABLE,
    "NAME" VARCHAR2(100 CHAR),
    CONSTRAINT "PEOPLE_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE
  )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "USERS" ;

Create a new sequence:

CREATE SEQUENCE "HR"."PEOPLE_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ;

Create a new trigger, associate it with the above sequence and bind it to ID attribute of PEOPLE table:

create or replace
TRIGGER "HR".PEOPLE_TR
before insert on "PEOPLE"
for each row
begin
if inserting then
if :NEW."ID" is null then select PEOPLE_SEQ.nextval into :NEW."ID" from dual;
end if;
end if;
end;

Next, we will use the below PL/SQL scripts to populate our PEOPLE table with 1 million rows:

declare
 id_1 PEOPLE.ID%type;
begin
    for i in 1..500000 loop
       select PEOPLE_SEQ.NEXTVAL into id_1 from dual;
       insert into PEOPLE (PEOPLE.ID,PEOPLE.NAME)
       values(id_1,dbms_random.string('U', 10));
    end loop;
end;
declare
 id_1 PEOPLE.ID%type;
begin
    for i in 1..500000 loop
       select PEOPLE_SEQ.NEXTVAL into id_1 from dual;
       insert into PEOPLE (PEOPLE.ID,PEOPLE.NAME)
       values(id_1,dbms_random.string('L', 10));
    end loop;
end;

Finally, create an index for the NAME attribute as follows:

Tuning, Testing and Conclusions

The autosuggest behavior will be tested with respect to NAME attribute.

In what follows we shall run different scenarios and analyze the performance gain/lose with respect to each one of them.

For each scenario a PL/SQL script – matching the View Criteria that provides service for the af:autoSuggest – will be defined and executed in a 100 cycles loop for a better view of the differences.

Each script will be run twice:  with index and without index defined for the  NAME column.

Scenarios

I. NAME uses STARTSWITH operator and has Ignore Case = unchecked:

Associated PL/SQL script:

DECLARE
 counter NUMBER;
BEGIN
FOR i IN 1..100 LOOP
SELECT COUNT(PEOPLE.NAME) INTO counter FROM PEOPLE WHERE PEOPLE.NAME LIKE 'X%' ORDER BY NAME ASC;
END LOOP;
END;

1. Index enabled:

2. After deleting the index:

As we can see the time needed to process the query when the index is enabled is way smaller than without the index.

II. NAME uses STARTSWITH operator and has Ignore Case = checked:

Associated PL/SQL script:

DECLARE
 counter NUMBER;
BEGIN
FOR i IN 1..100 LOOP
SELECT COUNT(PEOPLE.NAME) INTO counter FROM PEOPLE WHERE upper(PEOPLE.NAME) LIKE 'X%' ORDER BY NAME ASC;
END LOOP;
END;

1. Index enabled:

2. Afte deleting the index:

In this case the presence of the index does not provide any performance gain, the time needed to process the query in the 2 cases being ~ the same.

III. NAME uses Equal to operator and has Ignore Case = unchecked:

Associated PL/SQL script:

DECLARE
 counter NUMBER;
BEGIN
FOR i IN 1..100 LOOP
SELECT COUNT(PEOPLE.NAME) INTO counter FROM PEOPLE WHERE PEOPLE.NAME = 'LGHWNJDZTG';
END LOOP;
END;

1. Index enabled:

2. After deleting the index:

As we can see the time needed to process the query when the index is enabled is smaller than without the index.

IV. NAME uses Equal to operator and has Ignore Case = checked:

Associated PL/SQL script:

DECLARE
 counter NUMBER;
BEGIN
FOR i IN 1..100 LOOP
SELECT COUNT(PEOPLE.NAME) INTO counter FROM PEOPLE WHERE upper(PEOPLE.NAME) = 'LGHWNJDZTG';
END LOOP;
END;

1. Index enabled:

2. After deleting the index:

As we can see the presence of the index does not provide any performance gain, the time needed to process the query in the 2 cases being ~ the same.

V. For the cases when CONTAINS and ENDSWITH operators were used there were no semnificative differences in performance, the time needed to process the query with and without an index defined being ~ the same.

More about Oracle query optimization when using CONTAINS (%str%) and/or ENDSWITH (%str) can be found here.

Now, if for your particular case defining an index results in a speed increase when executing a search than you should consider that when an index is defined time needed for Insert, Update and Delete may increase because every time a new record is Inserted/Updated/Deleted the hash table containing the indexes must also be updated.

Also, keep in mind that all the above tests have been executed on a table with 1 million records and into a loop of 100 cycles.

Tags: , , ,

Leave a Reply