Wednesday, August 24, 2011

Proactively Controlling ADF Query Execution with Estimated Row Count

In ADF BC we can configure how many rows VO should return, however in the background it still will execute entire SQL statement without attaching rownum < X to it. We can prevent this by overriding executeQueryForCollection method and checking estimated row count, before invoking actual SQL statement:


As you can see, if estimated row count is higher than expected limit - we are throwing our custom JboException. This exception is handled in overriden DCErrorHandler class and reported nicely to the user:


You can declare your custom DCErrorHandler class through DataBindings:


VO contains View Criteria, where we are checking for Salary attribute to be > X. Criteria attributes are set to be selectively required as well, to prevent blank searches:


Blank searches will be restricted:


If user enters such criteria, which will bring result set exceeding predefined limit, application will terminate SQL execution and show warning message to the user:


In case if search criteria brings less results than predefined limit, SQL is completed successfully:


Download sample application - ADFProactiveQueryPerformance.zip

1 comment:

Nick said...

Nice tuning tip Andrejus. Thanks!

All the best,
Nick