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: