Tuesday 3 July 2007

OAF Key Do's and Don'ts (Part 2) - Performance Tuning: "Top 10" OA Framework Development Rules

1) ALWAYS use design time view objects (VOs) rather than dynamic VOs. Dynamic VOs have to be described by BC4J through an additional execute call for the VO prepared statement, and they potentially result in non shareable SQL due to different combinations.

2) ALWAYS set precision for all columns in the VO. This reduces the VO memory footprint. Without a set precision, for example, all String columns default to 4KB in size each.

3) AVOID calling VO.getRowCount to check for existence. getRowCount causes the entire VO row set to be fetched back to middle tier.

4) NEVER call VO.getEstimatedRowCount. getEstimatedRowCount executes select count(*) on the VO. This count is not guaranteed to stay the same, thus the method name.

5) ALWAYS call registerOutParameters with proper precision for callable statements. This reduces the callable statement memory footprint. Without a set precision, for example, all varchar columns default to 32KB.

6) ALWAYS use Oracle-style binding (:1, :2) in your SQL and DO NOT use JDBC style binding (?). This avoids parsing SQL at runtime to do String replacement.

7) AVOID coding generic VOs just to try to reuse them. The "one VO fits all"approach adds complexity to your SQL, increases number of attributes and VO memory footprint.

8) DO NOT execute searches by default nor allow blind queries.

9) Use PNG format not JPEG for BI graph beans.

10) Use JDeveloper to profile your code. JDeveloper 9i has memory, CPU and
event profilers.