ADF Dynamic Query Performance Considerations

Recently, I noticed a poor implementation of dynamic query in our application environment.  As the query was complex and its where clauses were generated dynamically, the developer chose not to implement the bind variables.  Now, some of you will be aware of the disadvantages of not using bind variables, but i have mentioned them below just to recap:-

• It is a security risk as it allows SQL injection attack
• It causes a major bottleneck on database as every time a search is submitted, the query will need to be parsed and for this the database acquires a latch.  A latch is like a synchronization code, so at a time only a single query might acquire the latch. thus, if you are parsing the query regularly, you are slowing down your database unnecessarily.
• Every time the query is parsed for different values a new statement will be created and stored in shared SQL area, so you have added memory problems to the list of issues.

Now that we have seen the issues, the solution then is to use the bind variables.

The problem:  Search was complex and developer did not know how many different clauses will be applied at runtime as the where clause depended upon the field values entered in the criteria.  We could use named bind variables but what happens if the search is executed again ?

The solution:  In case we use named bind variables and add them to the where clause, executing the query again with different criteria will cause JBO exception, because the bind variable will be there but it obviously would not have any value. To fix this, the simple solution then is to remove all bind variables at the start of your query and then add them depending upon the conditions. The code snippet below shows how to handle this.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

//In the backing bean

public void doSearch(ActionEvent ae){
DCBindingContainer bc=(DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
DCIteratorBinding iteratorBinding=bc.findIteratorBinding("YOUR_VO");
ViewObject voImpl=iteratorBinding.getViewObject();
//Get all the bind variables that we had added
AttributeList whereClauseParams=voImpl.getNamedWhereClauseParams();
//Get their names
String []attribNames=whereClauseParams.getAttributeNames();
for(String attribName: attribNames){
voImpl.removeNamedWhereClauseParam(attribName);
}
StringBuilder whereClause=new StringBuilder();
//dummy
whereClause.append("where 1=1 ");
//now append named bind variables to query
if(empId==1){
//a like comparison bind variable
whereClause.append("and empName like upper(:bEmpName)||'%'");
}
//define the created named bindvariable
vo.defineNamedWhereClauseParam("bEmpName",empName,null);
//invoke am's method to execute the query ?
.....
// In AM
query=query+whereClause;
//execute our defined query
voImpl.setFullSqlMode(ViewObjectImpl.FULLSQL_MODE_AUGMENTATION);
voImpl.setQuery(query);
voImpl.executeQuery();

That’s it now you can run your dynamic queries without bugging your DBA.