tag:blogger.com,1999:blog-4214922889450630344.post892965719237419004..comments2008-06-23T10:02:36.940-07:00Robin Clowershttp://www.blogger.com/profile/09558658468617627887noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-4214922889450630344.post-24210484456497402322008-06-23T10:02:00.000-07:002008-06-23T10:02:00.000-07:00Hi again merlin, it seems like you may be misunderstanding how Linq to Sql uses deferred execution, so I posted an <A HREF="http://robinclowers.blogspot.com/2008/06/deferred-execution-in-linq-to-sql.html" REL="nofollow">explaination</A> of it.Robin Clowershttp://www.blogger.com/profile/09558658468617627887noreply@blogger.comtag:blogger.com,1999:blog-4214922889450630344.post-92152763170078038272008-06-10T02:35:00.000-07:002008-06-10T02:35:00.000-07:00Thank 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.<BR/><BR/>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).<BR/><BR/>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.<BR/><BR/>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.<BR/><BR/>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 <B>not</B> change during the iteration of the data.<BR/><BR/>Thank you for this interesting conversation, Robin. The research was fun and educational.merlin981http://www.blogger.com/profile/05516602905783627586noreply@blogger.com