File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Caching EJB stored procedure calls.

 
Patrick Finnegan
Ranch Hand
Posts: 179
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We have an application that is using a stateless session bean to call a stored procedure using straight JDBC.

Sesson bean calls a class which has

connection = getDataSource().getConnection();
Proc = connection.prepareCall(spString);
rs = proc.executeQuery();

The problem is that the none of the data retrieved by the SP is cached at the JVM level. All calls result in a round trip to the database even if the same search is executed twice in a row and brings back the same data each time. This kills performance. I have read some of the posts describing how stored procedures can be called from EJBs i.e stateful beans but it is not clear if the EJB will cache the results of a stored procedure if the search parameters are the same.

Any suggestions on the best strategy to follow?

The ejb spec is 2.2
The App Server is WebSphere.
 
Marco Ehrentreich
best scout
Bartender
Posts: 1294
IntelliJ IDE Java Scala
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Patrick,

why do you expect any caching when using stored procedures? Usually stored procedures just give you the possibility to save some complex queries (maybe consisting of more than one client command) on the server side. This often speeds up executing the stored procedure again but it doesn't avoid a real round-trip to the database server. And I think that there's no big difference with session beans executing a stored procedure.

I guess to solve the performance problems you'll have to use some additional caching framework or create your own caching mechanism (if it's even possible or reasonable to do caching!).

Marco
 
Reza Rahman
author
Ranch Hand
Posts: 580
5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Patrick,

It sounds like you are confused about the WebSphere prepared statement cache. That just caches the "compiled" statement itself, not the results of executing the statement. If you can't move to JPA right now, look into caches like EHCache or Coherence.

Regards,
Reza
 
Patrick Finnegan
Ranch Hand
Posts: 179
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not referring to the WAS prepared statement cache.

Data returned by EJBs is cached in the JVM memory. Works like the Hibernate Level 1/Level 2 cache. So the data returned by a stored procedure called by an EJB would be cached in memory. That's the performance gain.
 
Patrick Finnegan
Ranch Hand
Posts: 179
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Persistence Manager may do the trick.

http://www-01.ibm.com/support/docview.wss?uid=swg21189749
 
Tim Holloway
Saloon Keeper
Pie
Posts: 17616
39
Android Eclipse IDE Linux
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want to cache individual rows, that's what Entity EJBs are for. In essence, an entity bean is a JavaBean that's effectively managed as though it was a member of a hash whose keys are the primary keys of the EJBs.

Entity EJBs can back the results of a stored procedure execution, although just for the record, I don't recommend stored procedures unless there is a compelling reason to use them. I've experienced some of their liabilities firsthand recently.
 
Patrick Finnegan
Ranch Hand
Posts: 179
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Got a reply from IBM and there are no extensions in WAS that explicitly Stored Procedures so we are going to experiment with CMP EJBs. We may check out Hibernate as well.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic