2016-04-29

Handling very slow execution of CreateInsert in JDeveloper 12.2.1

Many of you might encounter a serious performance issue with the createInsert functionality of ADF in 12.2.1. In detail:

If you have a large result set after a query (ex. a master data dialog) and you want to create a new record, the framework executes the findByKey method as many as four times  as there are records in the result set of the query (check Andrejus Baranovski's blog entry: Evil behind the ChangeEventPolicy PPR). This only happens, if you did not fetch all data up to the point of inserting a new row (so having -1 as range size will fix this, although querying many thousands of rows to create one seems not to be a good idea ;) ).

As we faced the same issue for a customer project, we tried to find out, what exactly is the issue here.

Preparation:

In our test we have the following conditions - A Table containing  ~ 70000 entries with randomly generated Char content in each column. For this, we created the default BC, so an Entity Object with the default settings, a corresponding default View Object and an Application Module.

On View Controller side, we have just a plain page with an af:table created by drag and drop from data controls. On the table, we set the following properties:

contentDelivery="immediate"
autoHeightRows="10"
rangeSize=13 -> leads to property change of iterator in page definition

For completion of the preparation, we add the navigation and crud buttons to the ui.



When running the page and clicking on CreateInsert, we see, that this takes a long time (it can get significantly worse for real data).



On a second run (make sure a new session is built up), click last first and then the create insert. This takes some time for the last-button click, but the insert afterwards is done immediately. So what happens?

Problem description:

The newest version of ADF has some changes in the Key mechanism. The most important (in general but also in our case) is that a key of a newly created  row is not null itself, but a Key Object containing null values, which seems like a good idea in most cases.

This change has a serious impact on the retrieveByKey in the ViewObject method, which seems to check if(key == null), which is never true in the new Version (since we always have a Key object at hand).

In case the data is not already fully fetched from the database, ADF creates a new RecordSet for the find by key method (which makes sense in case you are searching for a row that might not be already in the fetched Rows). In general this new View Object uses the constraints of the original View Object, but in most cases for master data dialogs, there are no real constraints in the first place.

In case of the insert, a findByKey is executed (coming from the af:table, which is refreshed by ppr; again, see the post of Andrejus), coming with a key that is not null, but contains only null values. Since the fetch size is obviously not reached (we have 13 rows out of 70000), a new View Object is created in background and executed for the findByKey, which never will be finding any Row. Strangely, this seems to be executed for each row that is in the range and before the currently inserted row. So in worst case, the query would be executed up to 12 times in the example.




Solution:

Personally, I think, Oracle must take a look at this and maybe change some implementations at this part. But as we know, it might take a while until fixing the issue. So here is a workaround. In your ViewObjectImpl (better, a base ViewObjectImpl that is superclass of all your View Objects) override the following method:

   @Override
   protected Row[] retrieveByKey(ViewRowSetImpl rs, String keyName, Key key, int  maxNumOfRows, boolean skipWhere) {
        if (!key.isNull() || keyName != null) {
            return super.retrieveByKey(rs, keyName, key, maxNumOfRows,    skipWhere);
        }
        return new Row[0];
    }

 
This is just a safety fallback, that if all of the key columns are null the findByKey mechanism is not executed at all. In all other cases (i.e. you really want to search a row by its key or part of its key), the framework default will do its job.

In the Example, we created the same UI with a View Object that has the fix (FixedRandomEntriesView). In this case, the create is done immediately, even if the fetch does not have all rows, wich solves our problem.

One should state, that this solution is only applicable for blank inserts (i.e. no DBSequences etc.). In this case, you have to put some further effort to the overriden method to check if you are in insert mode or search mode.



You can download the sample app here: CreateInsert12211 Example
To work with our example data, you can use this sql script to create the table and insert data: Random_Entries.sql

Keine Kommentare:

Kommentar veröffentlichen