Saturday, November 2, 2013

Find By Key and View Criteria Row Finder Methods vs. Get Row Method in ADF BC

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.

6 comments:

Ahmad Al-Zamer said...

Hello,
there 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?

Andrej Baranovskij said...

Yes, in 11g you could use findByViewCriteriaWithBindVars. However, in 12c is better to use Row Finder, as this sample is based on 12c.

Regards,
Andrejus

Ahmad Al-Zamer said...

Thanks.
but why is it better to use row finder? is it related to performance?

Regards,
Ahmad

Andrej Baranovskij said...

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.

The 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

Anonymous said...

Hi Andrejus,

Great 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

Andrej Baranovskij said...

Hi,

Good 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