Wednesday, April 4, 2012

Bad Practice Use Case for LOV Performance Implementation in ADF BC

If you want to learn something well, there is nothing better as to learn bad practices first. Really, by knowing bad practices - you will start to follow best practices just automatically, because there will be simply no bad practices left. If serious, I would like to post one use case that was discovered recently during my work - terribly slow LOV performance in ADF BC. After debugging session, it was discovered - when selecting LOV value, LOV SQL statement was executed around 20 - 25 times. With such SQL statement duplication, of course we can not expect great performance for LOV's, especially with complex SQL cases. Because of specific requirements, there were no EO Associations created to get LOV description, LOV was created based on transient attribute directly. However, this is not a problem, problem is elsewhere.

To reproduce this bad practice use case, I have created basic sample application - EO, VO, AM and LOV VO (there is no second EO and Association for LOV description):


Main VO - EmployeesView, contains transient attribute - JobTitle (always updateable). This attribute retrieves its value from Groovy method, this method in turn calls SQL statement to get LOV description value on page load. This is big NO NO NO, to use Groovy script for attribute value expression, when method invoked from Groovy expression in turn calls SQL logic. This is because, EO/VO attribute in ADF can be initialized multiple times during same request - it will call then SQL multiple times as well. Without knowing this, you can degrade system performance significantly:


Transient attribute is defined with LOV (this part is OK):


Custom method from AM (invoked through Groovy script as attribute expression), calls LOV VO and executes View Criteria to retrive LOV description value to display:


On runtime, when user select value from LOV:


It calls same custom AM method through attribute value around 20-25 times, imagine how it may slow down your system:


This use case was implemented, because there are no EO Associations in the system. In my next post, I will describe how to implement this use case with inline SQL for optimal performance.

Download bad practice sample application - LOVByNameGroovy.zip.

2 comments:

Hasim said...

That is why it is best to use List of value binding instead of groovy for this purpose. However I would suggest groovy for calling method adf.object.appmdoule.webservicesproxymethod(vo attribute parameter) that calls webservices method. what do you think andrejus ?

Andrej Baranovskij said...

Hi Hasim,

If this Groovy method is declared as VO attribute value, it will be executed many times and in turn will invoke Web Service many times.

You should put a logger message into webservicesproxymethod and see how many times its invoked. If its 1 time, then its no problem.

Regards,
Andrejus