You may have seen my previous blog post - WebLogic Stuck Thread Case - Large Fetch Generated by Get Row ADF BC Method. Blog reader was asking if it would be a solution to use instead of Get Row method, alternative methods - Find By Key, or View Criteria. Yes, these two methods perform much better comparing to Get Row, and there is no full intermediate row range scan.
I would like to present updated sample application, where these two methods mentioned above are implemented and tested - LargeFetchApp_v4.zip. Application module implementation class contains two methods, one for Find By Key (intentionally, I'm using key to be at the end of row set) and another for Row Finder based on View Criteria. If I would use View Criteria directly, rowset will become filtered, instead I'm using 12c feature Row Finder, it allows to use View Criteria to search for rows in the current rowset:
I'm using JDEV 12c, this is how Row Finder is defined (you can read more about Row Finder in my previous post - ADF BC 12c New Feature - Row Finder):
Row Finder is using View Criteria, search is done by the same key as in Find By Key method:
We load ADF table and press Find By Key method:
Instead of fetching all rows up to a row with a key 10099 (as it happens with Get Row method), new SQL query is executed and one row is fetched - as we need:
Press Find By Criteria to test second approach with Row Finder and View Criteria:
View Criteria calls new SQL query to search by ID and returns only one row, instead of fetching all rows:
This shows - both method are good enough and perform better than Get Row method. You should choose one of these methods, based on use case requirements.
I would like to present updated sample application, where these two methods mentioned above are implemented and tested - LargeFetchApp_v4.zip. Application module implementation class contains two methods, one for Find By Key (intentionally, I'm using key to be at the end of row set) and another for Row Finder based on View Criteria. If I would use View Criteria directly, rowset will become filtered, instead I'm using 12c feature Row Finder, it allows to use View Criteria to search for rows in the current rowset:
I'm using JDEV 12c, this is how Row Finder is defined (you can read more about Row Finder in my previous post - ADF BC 12c New Feature - Row Finder):
Row Finder is using View Criteria, search is done by the same key as in Find By Key method:
We load ADF table and press Find By Key method:
Instead of fetching all rows up to a row with a key 10099 (as it happens with Get Row method), new SQL query is executed and one row is fetched - as we need:
Press Find By Criteria to test second approach with Row Finder and View Criteria:
View Criteria calls new SQL query to search by ID and returns only one row, instead of fetching all rows:
This shows - both method are good enough and perform better than Get Row method. You should choose one of these methods, based on use case requirements.
Hello,
ReplyDeletethere is a method in adf 11g :findByViewCriteriaWithBindVars
isn't it possible to use this method to achieve the same result and performance as if you are using the Row finder?
Yes, in 11g you could use findByViewCriteriaWithBindVars. However, in 12c is better to use Row Finder, as this sample is based on 12c.
ReplyDeleteRegards,
Andrejus
Thanks.
ReplyDeletebut why is it better to use row finder? is it related to performance?
Regards,
Ahmad
For performance topic, we would need to test - I think it is same. But it is better, because Row Finder is supported through the wizard and is official way to do it. API method is also good, but this approach is bypassing wizard option supported out of the box. At the end it allows easier maintenance, while exposed through the wizard with Row Finder.
ReplyDeleteThe key part of this post is to provide alternatives with better performance to Get Row method. Your suggested method is one of alternatives and is valid approach. However, Row Finder is out of the box supported by 12c and is the way to go in such use cases.
Andrejus
Hi Andrejus,
ReplyDeleteGreat fan. Would like to thank you for all the posts you are doing. They are of great help. Coming to my doubt, findByKey() method is used to find a particular row in a VO. Why does it return an array by default? Isn't findByKey() is supposed to return unique(single) row?
Thanks,
Sai
Hi,
ReplyDeleteGood point. I think you can construct Key object with multiple attributes, not with key only - it could execute query with multiple bind vars and return different set.
Regards,
Andrejus