Tuning Database Access for CMP Entity beans
Database Administrators always claim the first step in tuning your
commercial applications is tune your SQL. However, tuning database
access for CMP entity beans are said easier than done! The first question
that springs to every body’s mind ” SQL are generated by the EJB
container then how can I tune the SQL”. In this article I will describe how
you will go about tuning the database access by CMP entity beans. I will take Oracle database and OC4J as the example however this can be applied to any database and any J2EE containers.
The first step in tuning the database access is find the SQL statements
generated and executed by the container. You can find these SQL by
either increasing the debug level for your container or using a tool like
P6SPY (http://www.p6spy.com) to work in conjunction with your
container.
Following are few tips to tune database access for your CMP entity beans. Making good friendship with your DBA
always helps and he will teach the tips and tricks of his trade.
Make sure your finder/select methods use indexes
Index scan can dramatically improve the performance of SQL statements so make sure that the SQL statement generated by your finder uses an index instead of doing a full table scan. For example, if you have a finder that finds your entities by name and you are trying to find an employee named Debu, apparently the SQL that your
EJB-QL will get translated will become like some thing:
select * from EMP where ename='Debu'
Make sure that your name column is indexed. You can use the EXPLAIN PLAN utility provided by Oracle to make sure that your query is using an INDEX SCAN. These may be boring for you and you may seek your DBAs help.
You can setup SQL*Plus to automatically generate PLAN OF EXECUTION for your SQL statements at the end of execution with the following steps:
sqlplus scott/tiger
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> set autotrace on
If you do not have an index on the ename column the plan of execution for your SQL statement and statistics your plan will look like:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
131 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
However if you have an index the Execution Plan and statistics will dramatically improve as follows:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMP_NAME_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
Avoid findAll on entities based on large tables
When the findAll method is used, the container tries to retrieve all rows of the table on which the entity is based and thus a FULL TABLE SCAN occurs. Avoid using this method on entities based on large tables, as it will slow down your database.
Exploit statement caching
The Oracle database provides a feature that caches SQL statements. This reduces the parse time when the SQL statement is issued to the database multiple times. For example in OC4J you can enable statement parsing for your data-source by adding the statement-cache-size attribute:
<data-source>
...
stmt-cache-size="100"
</data-source>
Enforce primary key constraint at database level
Several EJB containers maintain the uniqueness of entity beans and do not rely on the existence of primary key constraints in the underlying database table. When a new bean is created, the container creates a new database record. Before writing this to the database, the container issues a SELECT statement to make sure the insert does not lead to a duplicate entry. Primary key constraints should be enforced at the database level, which avoids the extra SELECT statement.
For example, you can disable this extra constraint checking by OC4J by setting do-select-before-insert=”false” for your CMP entity bean.
Use DELETE CASCADE at database level
When an entity with relationships is deleted with multiplicity ONE, the container is responsible for deleting all dependent entities. The container generates and executes the required SQL code to remove all of the related child records from the database. Try to exploit the native cascade delete facility in the Oracle database; this avoids multiple delete statements when removing the dependent records and CASCADE DELETE in the database level is much faster. The database removes all related records when the CASCADE DELETE option is specified for the table.
Set pre-fetch size to reduce roundtrips to the database
Oracle Database JDBC drivers have extensions that let you set the number of rows to pre-fetch to the client while a result set is being populated during a query. This reduces the number of roundtrips to the server, and can drastically improve the performance of finder methods that return a large number of rows.
You can set the pre-fetch size for your finder method in OC4J as follows in the vendor specific deployment descriptor:
<finder-method query=”" prefetch-size="15" >
<!-- Generated SQL: "select * from EMP where EMP.ENAME = ?" -->
<method>
<ejb-name>EmployeeBean</ejb-name>
<method-name>findByName</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</method>
</finder-method>
Define your own SQL Finder method
Several containers provide a way to define your own SQL for the finder method or an ability to override the SQL statement generated by your container. You have to use this in caution as this may have adverse effect on your application. You can write your highly optimized SQL statements for your finder method.
Conclusion
There are several other ways to improve the database access for your applications. You have carefully to examine the features offered by container to exploit these. Do not miss my JavaOne presentation this year where we will provide several tips and tricks to optimize CMP entity beans.
4:01:09 AM 
|
