June 5, 2008 at 11:42 am
With all of the databases that I have managed over many years the premise has been that optimal data access is obtained through the use of stored procedures because they are preparsed, etc. This provides optimal performance and response to the given query.
Now I am working with a project that is using JPA. I understand that persistent objects is the newest technology, but from what I can see no matter how optimized the JPA code may be, isn't the net effect that the JPA object is passing an adhoc query to the database? Wouldn't this then result in poor performance especially on large databases? Are there any statistics that compare a stored procedure access to a large database as compared to JPA?
Though this is not a performance statement per se, but do we not violate any number of database design tenets by allowing direct access to database tables? Granted the table can be added to a role to control access, but the underlying tenet of database design is that there never be direct access to the underlying tables.
June 5, 2008 at 12:38 pm
SQL 2005 caches many query execution plans as well as stored procedure plans. Stored procedure plans persist longer where query execution plans get flushed and replaced based on things like memory pressure, activity using them, etc. This does not hapen for all queries, but JPA built queries will be parameterized and prepared so they are pretty likely to cache execution plans. So, you may find that there is no performance difference, or a minimal one considering most performance problems in a database are not caused by a lack of having cached query plans - they tend to be caused by poorly written queries or plans that have been cached for too long and are using outdated statistics.
Stored procedures tend to offer a level of security in that you have pretty distinct control over the access to the database. If someone has security to access a table, they have a better chance of baing able to do something they are not supposed to vs. having a stored procedure that can only do one very specific thing.
So, implemented CORRECTLY, direct access to tables by an application layer is not necessarily bad. In fact, with SQL 2008 and LINQ to SQL, Microsoft is heading this direction in many ways. In addition to this, for ADO to use it's default locking behavior and for many of the basic data controls to function fully, you need to connect them to tables rather than using stored procedures.
However, it has been my experience that it is far easier and more reliable to manage developers writing reliable, well-performing code through accessing data using stored procedures.
June 5, 2008 at 12:51 pm
I haven't used JPA, but our developers have been making noises about nHibernate. I did some tests with it. The basic parameterized queries ran similarly to stored procedures. But the way those queries were built by the nHibernate tool would have lead to lots of wasted plans and possibly recompiles. I detailed the tests a bit here[/url]. You should look for similar issues.
For what it's worth, MS's foray into this sphere isn't LINQ to SQL nearly as much it's Entity Framework.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply