There are use cases, when data in DB is changed by background processes and we would like to display to the user latest data available. Very common implementation for this use case is to re-execute ADF iterator and VO each time when Task Flow or UI screen is accessed. Obviously this works, but performance would suffer - usually there is no need to re-fetch data each time, it must be re-fetched only when changes are detected in DB. ADF BC provides out of the box such functionality - it can detect changes in DB and re-execute VO through Database Change Notification. Make sure to grant CHANGE NOTIFICATION system privilege to the data source user.
Auto refresh functionality for ADF BC VO is working when DB pooling is disabled in AM tuning. This would mean you should use Auto refresh carefully and plan dedicated AM's.
I'm going to describe the use case and how you could benefit from VO auto refresh. Here is the example of typical ADF Task Flow with initial Method Call to re-execute VO and fetch fresh data from DB. In the next step UI fragment is rendered, where recent data is displayed:
This is how it works on UI. Departments TF is accessed by pressing Departments button in Employees table screen. This will trigger Execute action call in Departments TF and force VO to reload:
List of Departments will display recently fetched data from DB:
Each time when Departments TF is opened, VO executes SQL query and re-fetches data. This can be observed from the log:
While this works fine, it is not great from performance perspective. We should improve it by using VO auto refresh functionality. Change default activity in TF to UI fragment, we don't need to invoke VO re-execute:
Select AutoRefresh=true property in the VO settings, this will enable DB listener event for this VO and will force re-execution only when it is required:
We are ready to test it. Change Department Name column value:
Navigate to Departments from Employees table:
New data will be displayed automatically in Departments list, even without invoke initial Execute operation. If you would open the same TF, when there were no changes in DB, it will load data from cache, without re-executing VO and re-fetching the same data rows:
Auto refresh functionality for ADF BC VO is working when DB pooling is disabled in AM tuning. This would mean you should use Auto refresh carefully and plan dedicated AM's.
I'm going to describe the use case and how you could benefit from VO auto refresh. Here is the example of typical ADF Task Flow with initial Method Call to re-execute VO and fetch fresh data from DB. In the next step UI fragment is rendered, where recent data is displayed:
This is how it works on UI. Departments TF is accessed by pressing Departments button in Employees table screen. This will trigger Execute action call in Departments TF and force VO to reload:
List of Departments will display recently fetched data from DB:
Each time when Departments TF is opened, VO executes SQL query and re-fetches data. This can be observed from the log:
While this works fine, it is not great from performance perspective. We should improve it by using VO auto refresh functionality. Change default activity in TF to UI fragment, we don't need to invoke VO re-execute:
Select AutoRefresh=true property in the VO settings, this will enable DB listener event for this VO and will force re-execution only when it is required:
We are ready to test it. Change Department Name column value:
Navigate to Departments from Employees table:
New data will be displayed automatically in Departments list, even without invoke initial Execute operation. If you would open the same TF, when there were no changes in DB, it will load data from cache, without re-executing VO and re-fetching the same data rows:
Download sample application - ADFAltaApp_v9.zip
Great post ,
ReplyDeleteCan you please elaborate bit on this following statement
Auto refresh functionality for ADF BC VO is working when DB pooling is disabled in AM tuning. This would mean you should use Auto refresh carefully and plan dedicated AM's
So in order to avail this feature do we need to create separate am and attach vo to that am and disable am polling on that am
Correct me if I am wrong
Yes, you are correct. This means you should select specific use case, not do it for whole system.
ReplyDeleteRegards,
Andrejus