ADF Anti-Patterns: Dangerous Tutorials - Real Experience in ADF

Florin and me were presenting two sessions on UKOUG - UKOUG Tech13 - Red Samurai Real ADF Experience Sessions. There was quite a lot of content described and demoed, this is why I decided to split it into two different posts. This post will be about ADF Anti-Patterns: Dangerous Tutorials. The main idea behind this session was to describe most common misunderstanding hidden by typical ADF tutorials available on the Web.

We have prepared special app for this session, separating each use case into different module. You can download complete sample application - DangerousApp.zip.

Here is the picture of us presenting:

You can check uploaded slides for more details, below I will describe in addition every point presented. All the code listed is part of sample application, you can download it and check directly.

1. Batches Of and Slow Query

I believe you had experienced such situation, when SQL query was executed fast in SQL Developer, but slow in ADF. You should remember - ADF is executed from the server and there is additional roundtrip to bring data from DB. Also it depends how many rows are fetched in result set from DB to the server. By default, Batches Of property is set to 1:

We recommend to increase it for better performance and communication between WebLogic server and DB - this would allow to bring more rows in less roundtrips. There is a way to do this task in generic way, programatically - check the source code of sample application:

If you want to track time taken to execute VO, you could do this from the same executeQueryForCollection method - simply get start, end times and log:

2. Large Fetch

You can monitor row fetches performed in ADF with standard ADF BC method - createInstanceFromResultSet:

We would recommend to disable full table scroll by setting RowCountThreshold property for iterator in Page Definition to -1. Read more about this here: How To Disable SELECT COUNT Execution for ADF Table Rendering:

Make sure -1 is not set for LOV ListRangeSize property, otherwise it will be fetching all rows from DB, when LOV popup will be opened. Read more about it here: Fix Rowset is Forward Only Error for ADF BC LOV Range Paging (11.1.2.1.0):

3. Groovy Misuse

Keep in mind - when using Groovy function, it calls basic SQL statement, fetches records into memory and performs intended function. This would mean, it could fetch lots of rows into memory and only later produce requested result. Instead, you should consider using optimised SQL query directly for better performance, without using Groovy function.

Here is Groovy sum function example:

From the log you could see basic SQL statement executed and all rows fetched (2 rows in this example, it will be much more in real life scenario):

Another possible slow performance case with Groovy - calling Java method from Groovy attribute value expression. Especially if this Java method in turn is calling VO to fetch data from DB. The problem is related to numerous invocation of the same method by Groovy expression defined for attribute value. Here is example of such Groovy expression calling Java method from attribute value:

Java method is basically calling VO and executing it:

From the log we can see - VO was invoked and SQL executed at least two times in this example:

4. Passivation/Activation

When rendering ADF table, by default two requests are made. Between these requests, passivation/activation may happen and this going to slow down application performance. We could minimise this up to one request by setting ContentDelivery=immediate for ADF table, to prevent unnecessary passivation/activation. You can read more about it in this post: Immediate Effect for ADF Table Content Delivery:

ORDER BY and large fetch related issue was described in addition, it is document in this post: Reproducing WebLogic Stuck Threads with ADF CreateInsert Operation and ORDER BY Clause

5. ADF Query Misuse

Make sure not mix-up Bind Variables in VO, while using them from View Criteria. In this case I demo wrong scenario - Bind Variable is defined as Required, Where type:

This Bind Variable is used later from View Criteria. For Required, Where type Bind Variables, JDeveloper allows to choose Ignore Null Values option. Eventually this will break DB index usage, as IS NULL will be applied for the column: