I just saw merlin981's comment on my LINQ to SQL post, thanks for taking time to leave it! That said, I think the term "Best Practice" is something of a misnomer here. There has been much written on both sides of this debate. One thing is for sure, though, a parameterized query is compiled just like a stored procedure on SQL Sever version 7.0 and on. From Frans Bouma's blog, I found this article in the SQL Server's Books Online:
SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.
So I think it is clear that sprocs will not be significantly faster than ad hoc SQL for simple cases. This is not to say that you should never use sprocs, on the contrary, there are situations where sprocs will be the only good solution (for instance, complex data manipulation that requires temporary tables). The point is that using an ORM can make development easier by allowing you to ignore the SQL for the majority of cases. If you see that parts of your application are slow, then you can fix that.
Merlin also mentioned that running queries directly against tables uses deferred execution like it is a bad thing. Deferred execution is what allows LINQ to work at all, and can improve performance in many scenarios. Of course, like any tool, it can get you into trouble if you don't understand it.
2 comments:
Thank you for the response. I concede you are correct that stored procedures are not pre-compiled in SQL Server 2000 and 2005. This is definitely an under-discussed change from previous versions.
However, I do still believe stored procedures are better, if for no other reason than security. First, for the added layer of security from SQL injection attacks. Second, from the added layer of internal security (you can grant execute permission to the IIS user for stored procedures, but deny execute/insert/delete on all other SQL commands).
Regarding deferred execution, I can see it as a good thing in a limited number of scenarios. The problem with deferred execution is that developers (especially ones new to LINQ), are accustomed to knowing that when they have a List or other IENumerable/IQueryable object, the data is already there. However, with deferred execution, the data is not "there" until you request that particular element. Also, especially with SQL, the element can change before you make the request for it.
The majority of the time, the developer wants (and probably needs) the data to be there immediately. Without properly understanding deferred execution, the developer could think that his IQueryable object is fully populated, when it is not.
There is also a performance penalty, in my opinion, for deferred execution on LINQ to SQL. The problem comes when you iterate through the list; you are constantly querying the database. Constantly requesting one row at a time can have serious consequences on a production database that services several hundred users per minute. This is another advantage of stored procedures (for LINQ to SQL), because the sproc forces the database to return all the data back to LINQ immediately. In this way, regardless of deferred execution, the developer can be assured that a) he/she has only queried the database once, and b) all the data is available and will not change during the iteration of the data.
Thank you for this interesting conversation, Robin. The research was fun and educational.
Hi again merlin, it seems like you may be misunderstanding how Linq to Sql uses deferred execution, so I posted an explaination of it.
Post a Comment