Comparing Number of SQL Executions to get LOV Description With Entity Association Available

This post is logical continuation for my last post - Comparing Number of SQL Executions to get LOV Description Without Entity Association Available, where I was comparing number of SQL executions for different methods without involving EO association. Today I will check how it works when EO association is available. Keep in mind, all what is discussed in previous and this post is related to the case when we define LOV on description field directly, without showing ID value (most common use case, from my experience).

Two methods will be compared:

1. Standard ADF approach to use Association for VO join and retrieve description. Documented long time ago here - Defining the LOV on a Reference Attribute in Oracle ADF 11g. Updated sample application to JDev 11g R2 - LOVByName_v2.zip. (Association VO Join)

2. Transient EO attribute approach with Groovy, based on EO association. Documented previously as well - LOV Description Text with Groovy. Updated sample application to JDev 11g R2 - LOVByNameGroovyAssoc_v2.zip. (Association Groovy EO)

From the first look, standard approach seems to perform better (4 SQL execution vs. 5). However, I'm a bit disappointed with ADF bug in standard approach - it executes some weird SQL statements and can cause real runtime performance issues (read below for detail description, registered in Red Samurai Oracle bugs Wiki - WHERE by Description for LOV). Number of SQL executions to retrieve Job description for test case described below:

As we saw from previous post, inline SQL execution to get LOV description still runs much better - just 1 SQL execution vs minimum 4 execution for LOV description with EO association:

However, you may get easier maintenance when using EO Associations, don't underestimate this. There always must be common sense, choose optimized solution, only where performance is critical.

Both sample applications from this post are tested in the following way - initial page load with LOV component, navigation to the next record, LOV opening, LOV value selection (same as applications without EO Association available):

A. Initial page load with LOV component

B. Navigation to the next record, with different description (Next button)

C. LOV list opening

D. Value selection from LOV list

A. Initial page load with LOV component

Association VO Join - 0 separate SQL for Jobs, description retrieved from join:

Association Groovy EO - 1 SQL for Jobs:

B. Navigation to the next record, with different description (Next button)

Association VO Join - 0 separate SQL for Jobs, description retrieved from join (total = 0):

Association Groovy EO - 1 SQL for Jobs (total = 2):

C. LOV list opening

Association VO Join - 1 SQL for Jobs (total = 1):

Association Groovy EO - 1 SQL for Jobs (total = 3):

D. Value selection from LOV list

Association VO Join - 3 SQL for Jobs (total = 4). LOV value selection executes weird SQL's for this use case:

Association Groovy EO - 2 SQL for Jobs (total 5). Sometimes executes duplicate SQL, searching by key (still can be considered to be better approach comparing to standard, because executes select by key only):

Standard approach (Association VO Join) works well, until you need to select value from LOV. At this point, it executes multiple SQL's with WHERE clause ignoring DB indexes. For some reason, it scans DB records by description you are returning (in addition to the search by key). And even worse, it appends all fields shown in LOV to the WHERE clause through separate SQL.

First it executes in addition to regular SQL - scan by description field:

And then it executes one more SQL with WHERE clause containing all attributes visible in LOV:

All attributes declared as display attributes are included into WHERE clause - this may slow down complex query significantly:

To summarize, this is reproduced with VO based on joined EO:

Description field is added from joined EO, LOV is defined on description field:

Transient EO attribute with Groovy approach (Association Groovy EO), doesn't generate SQL on description, but it invokes more SQL statements with search by key. If standard approach is slow for you, consider this one - create transient EO attribute and reference description from EO Association using Groovy (documented here):

There will be no VO join created:

Display attributes will not be included into WHERE clause: