SQL For Programmers – Stored Procedures (Better than LINQ)

arct-06 [Don't start the flames until you read the entire post :) . ]

While we could go on with the various syntax elements in a SELECT statement, I doubt that it would be as helpful as the items we’ve already covered.  Most of what I use on a day to day basis is some combination of what I’ve shown already.

Where SQL really starts getting fun though, is with stored procedures.

But why use Stored Procedures at all?

The historic answer to this question has always been, “speed.”  Every time you send a query statement to the SQL server from your application, it has to re-compile it.  There are ways to compensate for this, but the code you’d need to write to do this is almost harder than just writing the stored procedure to begin with.

However, now that we have LINQ, it is really hard to make the performance argument any more.  In fact, the argument of the day is, “.NET Programmers don’t need to learn SQL, they should learn LINQ instead.”  And, if all you ever write is a basic SELECT, INSERT, UPDATE, or DELETE statement, you’d be right.  LINQ has all that optimization code built into it, so you won’t get any significant performance gains by moving to a stored procedure.

However, there are two reasons why I continue to argue for Stored Procedures over LINQ.

  • LINQ does not currently support all of the SQL syntax.  For example, there is no clean way to use the IN keyword with a Sub-Select like we discussed a couple of days ago in Finding IN a List.
  • There is no clean way of having LINQ process multiple tables and return just the data we are looking for.  Instead, you’d need to pull the data from the multiple tables, process the data in .NET and throw out the data you don’t need once you have your result.While that method works, it is sending more data “over the wire” than is necessary and is clearly a performance issue.

In short, the reason you want to use stored procedures is because you gain flexibility you would not otherwise have.

In the same way that using a three layer architecture gives you the flexibility and control you will need in your applications, placing your data retrieval in a stored procedure allows you to do things with SQL that you would not otherwise be able to do using LINQ.

This is not to say that I’m anti LINQ.  It definitely allows me to do things in .NET that would otherwise require many more lines of code.  I just don’t think it is the right tool for the job when it comes to retrieving data from your SQL database.

So, next week, we’ll start looking at the syntax for stored procedures and some of the things we can do in a stored procedure other than just wrapping our SELECT, INSERT, UPDATE, and DELETE statements.

Most Commented Post

One Response to “SQL For Programmers – Stored Procedures (Better than LINQ)”

Leave a Reply

Comment Policy:

  • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
  • Leave me pure spam and I'll delete it.
  • Leave a general comment and I'll remove the link but keep the comment.

Notify me of followup comments via e-mail

Bear