Sunday, December 11, 2011

Tuning ADF Tree - Retain View Link Accessor Rowset

When expanding ADF Tree node, by default - each time ADF generates new SQL statement to retrieve expanded tree level nodes. New SQL statement is generated even after collapsing and expanding same node. In most of the cases, especially when tree structure is static, we would like to avoid multiple SQL invocation for the same nodes. This can be achieved by setting Retain View Link Accessor Rowset option for View Object tuning.

Download sample application - RetainViewLinkAccessorApp.zip. This sample implements 3 levels tree structure: Locations -> Departments -> Employees:


When user expands same node of Locations (top level) two or more times, we want to retrieve previously queried Departments rows from cache, instead of querying from database again. Go to Locations tuning section and set Retain View Link Accessor Rowset setting - this will enable Departments rowset caching:


Set the same setting for Departments (second level), this will cache Employees rowset:


Employees View Object represents tree leafs and can't be expanded - means no need to set tuning option (there will be no children):


When top level Locations node is expanded, SQL is generated to retrieve corresponding Departments rowset (if Locations node will be collapsed and later same node expanded again - no SQL will be generated, rowset will be retrieved from memory). SQL is generated only first time, because of our tuning - it will not be generated during subsequent collapsing/expanding of the same node:


Tree structure:


8 comments:

Anonymous said...

Andrejus,

Will this not be a problem in case of Activation/Passivation ?

-Kamaal

Andrejus Baranovskis said...

Hi,

I have tested - no is not a problem. View Link accessor is passivated/activated automatically with the rest of VO data.

Thanks for question.

Andrejus

Kristian Shahinian said...

Hi,

I saw your post about view link accessors and detail VO from AM (http://andrejusb.blogspot.com/2012/05/view-link-accessor-usage-performance.html). I believe your conclusion was that detail VO is faster. How about using this "Retain View Link Accessor Rowset"? Does it change your opinion?

And another question. What happens if I modify the detail VO through the view link accessor, for example add new detail row or change an existing one.

Thanks in advance.

Andrejus Baranovskis said...

Hi,

Yes, detail VO is faster - is using data from cache, there is no separate SQL execution.

"Retain View Link Accessor Rowset" this option is good for another reason - for ADF trees. When user opening and collapsing same node - it will keep node children in memory.

I will double check your second question about accessor changes and will get back.

Andrejus

Andrejus Baranovskis said...

"What happens if I modify the detail VO through the view link accessor, for example add new detail row or change an existing one" - this will be synchronized with detail VO and displayed on UI.

Andrejus

Lakshmi Vara Prasad Hari said...

Hello Andrejus,

Did you try out the last comment on October 31, 2012 ?
I am also facing a similar issue, where the events (event change listner) is not getting fired, nor any changes to the existing detail rows is not getting reflected in the UI.

-Hari

Andrejus Baranovskis said...

This comment is from me and im saying it should work.

Andrejus

srivocab said...

I have a small query here..

I have VO1 , V02, V03

VO1 has a view link with VO2
VO1 has a view link with VO3

Now when i want to select the Retain View Link Accessor Rowset i want to retain only for the first View Link and not for the second one.

May i know how this can be achieved. i am in great urgency for this solution.

Any immediate solution is appreciated.

Thanks in advance
Srinivas P