Monday, February 2, 2009

OpenJPA and pureQuery

There's a hidden gem in WebSphere V7 that many of you may not know about. If you're looking for a way to improve performance of database-intensive applications that access DB2, you should probably take a look, especially if you're using z/OS and are looking to drive down CPU costs.

In WebSphere Application Server V7, we deliver an enhanced Java Persistence API (JPA) implementation that supports static SQL access to DB2.. This may not mean much to you, but your DB2 DBAs will certainly understand the implications of this. In general, Java database access, whether through JDBC or OpenJPA is all dynamic SQL. From a security and performance perspective, static SQL can be better. For some applications, it can be significantly better.

For those of you who have never heard of static SQL, Figure 1 says it all - static SQL is basically preprocessed so all the database access processing does not have to happen when real users are using the application. As well as reducing processing time, it also means that performance stays more consistent. There is no up and down of performance based on whether the query is in the SQL statement cache or not.

To take advantage of this feature, you don't need to change anything about your JPA application; however you do need to have the pureQuery Runtime. WebSphere AS provides the utility that generates the SQL from the persistence unit along and for any JPA named queries. For more on how to actually do this, see this article in the WebSphere Tech journal.

You can see some performance results of using static SQL with pureQuery in this article. More background material on Static SQL can also be found in this article.

To find out more about how pureQuery can work together with WebSphere to reduce costs and improve quality of service, be sure to tune in to this webcast, presented by my colleague in pureQuery-land, Steve Brodsky. You can also read his blog entry on the pureQuery and OpenJPA integration.

Enjoy,
Kevin

4 comments:

Chris Adkin said...

Kevin,

Any plans to integrate any Oracle specific performance features into WebSphere JPA, such as the array interface ?.

Chris

Kevin Sutter said...

Chris,
Our first goal was to provide the better integration between WebSphere JPA with pureQuery and DB2, especially in the area of static SQL support. But, we also know that Oracle has an extremely large following with our WebSphere customers. So, we are interested in supporting the advanced features of Oracle as well. How's that for a suitably evasive answer? :-)

Kevin

Kevin Sutter said...

Chris,
I've been doing a bit more digging into Oracle's array interface and I may need some additional information from you about your specific needs. From what I have found out, it looks to be very similar to standard JDBC batching support. We already provide that in our JPA solution. Is there something more to the request that I am missing?

Thanks,
Kevin

Lukas Eder said...

Interesting to know. Doesn't DB2 have a soft-parse mechanism that recognises similar queries before hard-parsing them again? I know that Oracle and SQL Server do a lot of SQL statement, cursor and even result set caching to ensure that dynamic SQL can be issued and still behaves like static SQL.

The other curious thing is pureQuery, I have hardly ever heard of this. Is it still maintained by IBM? How does it compare to something like http://www.jooq.org