As a DBA I have always used stored procedures to access database data for the myriad of reasons that we all are aware of. My current project has made the decision to forgo stored procedures and access data directly from the underlying tables by means of JPA.

I understand the concept of persistence, but I have seen any analysis that addresses my primary concerns of performance, reusable code, security, and standards. Everything that I have learned about database design over many years is that you never allow direct table access and to control who can access the data via stored procedures.

 With the JPA approach, tables are opened up for direct access. From what I have seen adhoc queries are buried in the objects. I have heard but not been able to validate is that the objects create cached query plans to allow for operations similar to that of stored procedures. Are there any studies that formally compare data access performance of stored procedures as compared to JPA?

Also, with these query statements buried in these objects does this not eliminate the concept of reusable code? In the development of stored procedures I typically create a set of low level tools that all procedures can use for common operations to eliminate duplicate code. Unless there is something that I am missing it would seem that this reusable code construct is eliminated and each different query would need to repeat common operations.

Maybe this is the new frontier but I have not seen and can not find any comparative studies that analyzes the performance of JPA to that of stored procedures especially with large complex databases.

Is there anything available to indicate that replacing stored procedures with JPA is the correct direction to take?
 

I haven't used JPA, but from what I could find out, it essentially an ORM, like Hibernate, and should be parameterizing the SQL being sent to the database server (you can easily validate that by running SQL Profiler when running an application using JPA to see the SQL being sent to the database server).

If indeed it does parameterize the SQL, performance shouldn't be a concern. There are other arguments for using ORMs with direct table access versus using stored procedures, and people who are passionate about both. Search the web for ad-hoc sql versus stored procedures and you will find more information than you might imagine. One of my more favorite items on the topic is an article-esque blog post by Ted Neward entitled "The Vietnam of Computer Science", which can be found here http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx.

Note: If it is not parameterizing the SQL sent to the database server, I would avoid using it.

I have always been one to use stored procedures over ad-hoc access for the very same reason you do - to prevent direct access to the underlying data objects (in other words, for security reasons). That being said, using an ORM such as JPA is certainly an option, as long as you are able to secure the database server in other ways (e.g., securing a subnet to only allow the middle tier to directly access the database server). No solution is perfect, and your specific environment, design, and implementation will certainly play a role in which direction you choose to go.