A colleague of mine recently had his transaction thwarted because the EJB container (OC4J) timed his transaction out. The default setting is 60 seconds, and he's still running the test in his own desktop with only a single user accessing the transaction. It is quite unacceptable for a transaction to exceed 60 seconds limitation while it is still in development stage.
The transaction includes many reads to the DB, before actually updating one record, and inserting many new records. As most developers who are unaware of the EJB transaction attributes and their respective meanings, he also set all his methods in the SLSB as Required. This would have all of the methods which are part of the transaction to be included in the transaction, when there are many cases that they may not. The transaction itself spans a great length, starting from the very first method in the SLSB, going through many other methods in the same SLSB, or in the other Session Beans, until finally return from the entry point method.
I tried to optimize this by learning the business algorithm behind the transaction, then changing all read accesses to NotSupported, and leave the write access to Required. This has significantly reduced the transaction span time. But still, I had to change the transaction time out limit to 4 minutes. This is still unacceptable.
After measuring the time it took for each processes, I found out that the transaction is trying to insert 15 records to a database table. Each record requires a FK field to be set, and each of this FK field has a different value (for the 15 records), and requires a database access to retrieve the object. Why? Because we're using ORM, i.e. Toplink.
In Toplink, I don't know how they do it in the other ORM technology, before you insert into a child table, you'll need to find the parent object, and set it as the FK field's value. Then, you insert the child.
This type of programming style leads to defficiency in many developers' code. So, what I did was I created another method to query these parent objects (based on whatever information I had), before I try to insert the 15 records into the database. I put the query result into a Map, which will be used by each of the 15 records as the lookup base for their respective FK field's value.
This approach worked very well. It reduced the 20 seconds access into 1.5 seconds. There are still many inefficient code lying around his package. But it's one refactoring. There will be more, of course. The transaction time out limit has been reduced back to 60 seconds, and all went well.
The question remains, does ORM tend to lead inefficient DB access, if performed recklessly?
Further Reading:
Head First EJB
Expert One-on-One J2EE Development without EJB
Bitter EJB
