Saturday, October 27, 2012

LOV Validation and Programmatic Row Insert Performance

When I work, I think - is not enough just to implement use case, final solution must have good performance and run without side effects. I will give you today one more example and technical hint, to prove such thinking is important. This time I will use example of programmatic insert, when newly inserted row contains attribute with LOV mapping.

LocationId attribute from sample application -, is mapped with LOV:

There is custom method created in AM Implementation class. This method is responsible to insert two new rows, it sets new value for LocationId attribute (attribute with LOV mapping):

So far so good - nothing special here. Use case of programmatic row insert seems to be implemented. But wait a bit, lets test it how it works - press Add Rows button on the UI, it will call our custom method and add two new rows:

It works - no errors. Well - thats true, but look what we can see in the log. We are inserting two new records, but there are 2 SQL (why 2 of them, at least we should be happy only 2 - but not 3. for me only 1 would be enough :) statements executed in addition per each new row. If you look more closely, you can notice these SQL statements are coming from LOV definition. Basically, because we are setting value for LocationId attribute (mapped with LOV) - ADF calls LOV SQL and executes it to validate new value we are setting (to check if it exists in LOV):

From LOV validation point of view this is logical and correct. But if we are performing bulk inserts and we know that attribute value always will be correct - there is no need to call LOV validation for each row. In such case I would recommend to use another VO, without LOV's defined - use it to do bulk insert and then re-execute VO with LOV's to display latest data on the UI.


Martin said...

Hi Andrejus,

and again a very useful entry.

I checked never the SQL-Staement for new created row with a LOV-Attribute. Now I will check though.

Many thanks!


Andrej Baranovskij said...

Thanks Martin - I'm happy is useful ;)

Reza said...

Hi Andrejus
Do not reduce performance if add another VO ?

Andrej Baranovskij said...

Not at all - number of VO's doesnt affect performance. Idea is to use plain VO, without LOV's for faster bulk insert.


Unknown said...


yes this will improve performance a lot of times. I tried this and its extremely useful when the entity row have many LOV defined


Unknown said...

Hi Andrejus,
I've exact same issue. I've a set of cascading LOVs. But the problem is all the attributes are in a transient VO.
And I use this as bindings on a page.
Any way to avoid LOV validation when programmatically setting values.

Thanks in advance.


Andrej Baranovskij said...

You may check this post:

Unknown said...

Thanks a lot, Andrejus. Worked like a charm!

Andrej Baranovskij said...

Happy to help.