Sunday, May 13, 2012

View Link Accessor Usage Performance Side Effect

View Link Accessor is available when there is View Link defined and typically is used in ADF to retrieve detail collection from master row in VO row implementation class. Officially this is recommended approach to retrieve detail collections programmatically. However, not everything what is recommended is always good from performance point of view. Its always good to question recommended best practices and compare with other possible solutions to achieve better performance. However, when focusing on performance - don't forget to keep your solution still maintainable and don't over complicate things.

In this post I will show you there is one additional SQL select executed, when using View Link accessor - looks like data is not retrieved from the cache by default, even if detail rowset is loaded already. Alternative solution with better performance will be presented as well - directly using detail VO instance from AM data model. Download sample application - ViewLinkAccessorApp.zip.

This sample implements test case for both approaches - detail collection retrieval from View Link Accessor and from VO instance declared in AM. As you see in the screenshot, there is Master record loaded and related detail rows are available. This means no additional SQL should be executed, when accessing detail rowset:


First I test to get detail collection from VO instance declared in AM - there is no SQL executed as expected:


Now I use standard View Link Accessor approach - it executes new SQL statement to get detail rowset which already exists in the cache. It executes this additional SQL once, on the second View Link Accessor call it keeps data in the cache - but why it executes new SQL statement, it could retrieve same detail collection from VO cache in first place:


Here we have source code for both methods. First one is using View Link Accessor and executes SQL statement, regardless if there is detail collection already loaded. Second is using detail VO instance from AM data model and always gets data from existing cache:


Optimized method instead of using View Link Accessor, is calling detail VO instance from AM data model directly - means it gets same data collection as displayed on the UI:


No comments: