LINQtoSQL vs Stored Procedures, Round 2

Now that we got the big security question out of the way, we can do a more subjective pro vs con comparison between the two methods of data access. Here’s a quick list that we’ve come up with in our proof of concept studies.

  • Con – Compilation – SProcs are pre-compiled; when you call your “create procedure” method, what you’re actually doing is telling SQL Server to compile & optimize the execution of your T-SQL so the server doesn’t have to do it on the fly later. LInQ does this partially by taking advantage of the sp_executesql function, but there’s always that initial hit when not in cache that will affect performance .
  • Pro – Maintenance – Any DBA will tell you how much of a pain in the ass managing sprocs between versions are. Hell, we’ve written an entire internal application based around database change management, and even with that we still have a fairly involved deployment process. So what if we didn’t have to do that anymore? Using LInQ, provided your user has appropriate access, you just write what query you need for the data operation at hand (usually in a few lines), and that’s it. Contrast that to the current process of creating a table definition, creating data adapters for the specific sproc you want to call, generating the corresponding code, and THEN implementing the data layer access. Not to mention all you need to do to deploy the DAL is compile a DLL or two (assuming DB users are setup).
  • Con – Maintenance – Along the same lines, if you DO need to change a data access method and you’re using LInQ, you need to recompile said DLL and redeploy it in the form of an application update. If using sprocs, it’s just a simple matter of tweaking the sproc (depending on the change needed) possibly without modifying code and involving clients.
  • Pro – Readability – It’s a lot easier to tell exactly what “from p in context.Products where p.ProductID == productID select p” does compared to “DataAdapter.Fill(dataSet, Adapters.SelectProductByID(productID))” – for all you know, that adapter might point to an entirely different stored procedure than what you expect, and in that stored procedure is a filter for products that don’t have accessories. To really ascertain what’s going on with a sproc, you have to open SQL Management Studio, find the one sproc buried in a list of 1000 sprocs, and modify it to see the script (which is displayed in a different language from your application).
  • Con – T-SQL usage – there’s a few things you can do with sprocs that you can’t do with LInQ. This has to do mostly with the fact that LInQ is database-agnostic, meaning it doesn’t know how to use t-sql specific stuff. Also meaning that some generated LInQ queries can be less-than-optimal compared to a sproc counterpart.
  • Pro – Debugging – this goes back to readability, but it’s very easy to see exactly what SQL that LInQ is planning on running against your server while debugging, whereas with sprocs at best you can see what parameters you’re handing to the server but not how they’re being interpreted
  • Con – Network Traffic – when you call a sproc, you just need to send the name of the sproc and parameter values. When you execute a LInQ query, you have to send the entire query to the database server, adding a lot to the traffic going back and forth and possibly affecting the performance of your data access layer.
  • Pro – Selective Data – we have multiple instances in our application where we need specific data from a row, but we always select the entire row (and possibly other data!) because that’s the sproc we already have setup. If we do want a more selective data fetch, we have to go about the maintenance path mentioned above and create a new sproc / table definition / data access call. But with LInQ we can specific exactly what data we need and it will rework the query to only select that data. In example:
var productNameAndSKU = from p in context.product
    where p.ProductID = productID
    select new {
        Name = p.Name,
        SKU = p.SKU

… will generate the following:

SELECT t0.Name, t0.SKU FROM dbo.Products AS t0 WHERE t0.ProductID = @p0

(this is why LInQ always uses specific column names as opposed to t0.* when selecting all columns). Also notice the new { } syntax – that’s what’s referred to as an anonymous type. Even when specifying it in-line like that, you can easily refer to productNameAndSKU.Name without having to do any reflection or type checking. And all of that without having to generate any new strongly-typed classes.

That’s pretty much all we had found between the two. You can see there’s obvious benefits to both approaches, so it’s really up to use to use the project, deployment options, and requirements to make an informed decision.