When speaking about performance, ADF developers should not rely only on the framework and do blind development. Its a must to check form performance proactively using different methods - AM pool disabled, JMeter stress test and finally monitor number of SQL executions. As for the basis for today post I will take bad practice for LOV implementation description - Bad Practice Use Case for LOV Performance Implementation in ADF BC, and will describe how to improve it.
Bad practice use case (AM custom method execution from Groovy VO attribute), you can download from link above. Improved solutions are available for download here - LOVByNameViewAccessor.zip and LOVByNameSQLEO.zip. So, there will be three solutions compared: AM custom method execution from Groovy VO attribute, direct LOV VO View Accessor Find By Key access and finally inline SQL approach to retrieve LOV description value. Keep in mind, all these implementations are not using EO Associations, this is because not always we can use EO Associations and I would like to make you think with this post about performance - so, you will not wonder why ADF system is slow. In my next posts, I will compare number of SQL executions for default LOV with EO Association available.
All three applications are tested in the following way - initial page load with LOV component, navigation to the next record, LOV opening, LOV value selection:
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
Here we have SQL execution results for all three methods (JOBS table access): Groovy script invocation from VO attribute for the custom AM method fails big time (Groovy value for VO attribute is initialized many times, this is causing to execute many times referenced method to retrieve LOV description):
Number in the graph presents total number of SQL executions (A-D tests).
A. Initial page load with LOV component
Groovy script invocation from VO attribute to call custom AM method - 3 SQL executions:
View Accessor and Find By Key - 1 SQL execution:
Inline SQL approach - no additional SQL for Jobs:
B. Navigation to the next record, with different description (Next button)
Groovy script invocation from VO attribute to call custom AM method - 5 SQL executions (total 8):
View Accessor and Find By Key - 1 SQL executions (total 2):
Inline SQL approach - no additional SQL for Jobs:
C. LOV list opening
Groovy script invocation from VO attribute to call custom AM method - 4 SQL executions (total 12):
View Accessor and Find By Key - 1 SQL executions (total 3):
Inline SQL approach - 1 SQL execution (total 1):
D. Value selection from LOV list
Groovy script invocation from VO attribute to call custom AM method - 9 SQL executions (total 21):
View Accessor and Find By Key - 0 SQL executions (total 3):
Inline SQL approach - 0 SQL execution (total 1):
Finished with statistics.
How to implement View Accessor and inline SQL statement approaches?
In both cases, you must have transient attribute - JobTitle. Just in the case of inline SQL - I prefer to have transient attribute on EO level - is easier to manage custom SQL statements and make them reusable. From maintenance point of view, I would recommend to use View Accessor approach. LOV declared for transient attribute should return both - key and description values:
In case of View Accessor approach, you should go to the getter method for JobTitle and include findByKey method there (search directly through LOV View Accessor RowSet to retrieve LOV description):
In case of inline SQL statement, just include SQL logic for attribute value on EO level and inherit it inside VO, where LOV will be defined:
Bad practice use case (AM custom method execution from Groovy VO attribute), you can download from link above. Improved solutions are available for download here - LOVByNameViewAccessor.zip and LOVByNameSQLEO.zip. So, there will be three solutions compared: AM custom method execution from Groovy VO attribute, direct LOV VO View Accessor Find By Key access and finally inline SQL approach to retrieve LOV description value. Keep in mind, all these implementations are not using EO Associations, this is because not always we can use EO Associations and I would like to make you think with this post about performance - so, you will not wonder why ADF system is slow. In my next posts, I will compare number of SQL executions for default LOV with EO Association available.
All three applications are tested in the following way - initial page load with LOV component, navigation to the next record, LOV opening, LOV value selection:
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
Here we have SQL execution results for all three methods (JOBS table access): Groovy script invocation from VO attribute for the custom AM method fails big time (Groovy value for VO attribute is initialized many times, this is causing to execute many times referenced method to retrieve LOV description):
Number in the graph presents total number of SQL executions (A-D tests).
A. Initial page load with LOV component
Groovy script invocation from VO attribute to call custom AM method - 3 SQL executions:
View Accessor and Find By Key - 1 SQL execution:
Inline SQL approach - no additional SQL for Jobs:
B. Navigation to the next record, with different description (Next button)
Groovy script invocation from VO attribute to call custom AM method - 5 SQL executions (total 8):
View Accessor and Find By Key - 1 SQL executions (total 2):
Inline SQL approach - no additional SQL for Jobs:
C. LOV list opening
Groovy script invocation from VO attribute to call custom AM method - 4 SQL executions (total 12):
View Accessor and Find By Key - 1 SQL executions (total 3):
Inline SQL approach - 1 SQL execution (total 1):
D. Value selection from LOV list
Groovy script invocation from VO attribute to call custom AM method - 9 SQL executions (total 21):
View Accessor and Find By Key - 0 SQL executions (total 3):
Inline SQL approach - 0 SQL execution (total 1):
Finished with statistics.
How to implement View Accessor and inline SQL statement approaches?
In both cases, you must have transient attribute - JobTitle. Just in the case of inline SQL - I prefer to have transient attribute on EO level - is easier to manage custom SQL statements and make them reusable. From maintenance point of view, I would recommend to use View Accessor approach. LOV declared for transient attribute should return both - key and description values:
In case of View Accessor approach, you should go to the getter method for JobTitle and include findByKey method there (search directly through LOV View Accessor RowSet to retrieve LOV description):
In case of inline SQL statement, just include SQL logic for attribute value on EO level and inherit it inside VO, where LOV will be defined:
No comments:
Post a Comment