LINQ queries vs Stored procedures [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 2 years ago.

开发者_JAVA技巧 Improve this question

What are the pros and cons of using linq queries(along with an ORM like EF or linq2sql) VS. Stored Procedures(SQL server 2008) to query and update a data model? Performance? Speed? Etc...

---

Linq is definitely more readable when you're in the code. Seeing a call to execute a sproc called "sp_GetSomething" doesn't tell you anything as a developer, unless you go and physically look at what the sproc does. seeing code like

var query = from c in db.TableName
            where c.Name == "foo"
            select c;

That tells you exactly what data is being pulled.

Stored procedures on the other hand do not require you to recompile the application if you decide to change the code. If you decide to suddenly change a "where" clause or change the Order By - changing a sproc is easy. Changing the Linq code could be more time consuming.

I'm sure there are plenty more, but these are two I've noticed.

---

There are 2 camps: for stored procs and against stored procs.

I've found that it is lack of experience that make people go one way or another. There are different kinds of shops where we develop.

In practice

• if you're a corporate programmer, then you'll never change your RDBMS platform. You refactor your client every now and and you'll reimplement your DAL/repository. Why? Use stored procs.
• if you work for a vendor, then you will probably have to support several RDBMS. An ORM abstracts this away mostly.

I'm in a corporate shop so...

• Pros: with Linq you don't have to know SQL
• Cons: you're screwed when things go wrong

We (as a developer DBA team) frequently have to bail out ORM users in sister teams.

There are also more subtle issues such that:

• stored procedures can be used by any client
• will outlast your refactor into EF or whatever .net 5 brings
• encapsulation offered by stored procedures to abstract schema away
• reduced round trips because shouldn't stored procs be treated like methods, or atomic calls?

---

@gbn has a great answer.

In huge systems with hundreds of millions of records, data manipulation on the presentation layer just isn't going to cut it. Data intensive applications (not blogging apps!) will need to scale sooner than you think.

ORM's are a huge time saver when developing a small to medium system. You can have a production ready app in no time with a properly implemented orm. no one likes to write crud.

It is also important to remember SQL syntax has been consistent for over 20 years. If this isn't important to you, you haven't been programming long enough.

---

I would almost always go with stored procedures for several reasons:

1) Using LINQ in code to query a database is not really following the principles of mult-tier architecture... anything involving accessing database objects should be done at the database level. LINQ queries are just a wrapper for writing SQL in your code. SQL or LINQ in code is a no-no, even though the MVC examples all do it.

2) Performance... stored procedures DO execute faster! Anytime that you are running queries from code, you are prone to scalability and performance issues.

3) Maintenance. Because stored procedures liberate you from having SQL or LINQ in your code, the maintenance for your stored procedures can be taken care of seperately (seperation of concerns).

---

The real answer is it's "horses for courses". From one perspective (as a db dev like @gbn), it's stored procs all the way. From my perspective as a back-office end-to-end web developer, who's app's concurrent usage never gets above 100, and who isn't brilliant at writing efficient SQL, it's LINQ (currently EF 4.1) all the way. And code-first, if I can get away with it. This is the 15th piece I've read on the matter today (after an argument in a dev meeting) and every single piece seems to suggest the same thing - "horses for courses". And sometimes, in a back-office world, it's not just data-fetching-efficiency that you have to take into account - it's how quickly (efficiently) you can fix bugs, and how easily (efficiently) the next person can see what your code is doing. From this perspective, LINQ wins hands down.

---

It depends on the SQL Version you use. The execution plan (which is the information used by SQL to make a second call faster ...) in SQL 2008 works really well also with an O/RM. I don't see why you would use an O/RM and waiste a lot of time by re-mapping everything to custom stored proc. Also a stored proc. is hardly to be TDD and doesn't allow you the concept of Unit of WOrk and transaction

---

I would agree with Peter Lee on this one - in a serious, enterprise-scale environment, using stored proc is probably the preferred way to go.

In addition to Peter's comments, here are a few more:

1. The EF or Linq-to-SQL (or NHibernate, too) are ORM's, which means they want to turn a row in your database table into an object. Since that object typically should contain all the values from that table (or even from multiple tables, in the case of EF), those ORM's usually use a SELECT * FROM .... approach. This means: since you're selecting all columns, you typically cannot make use of any covering indices, and your performance will hurt. It's a classic "convenience vs. performance" trade-off

2. All ORM's I know of and have worked with basically need full table access to the base tables. This is a big no-no for a lot of enterprises and their DBA's. Using stored procedures wisely, you can get by without having to allow all users base table access - a big plus in terms of access security!

---

Not really an answer to your question, but don't forget that you can still use EF on stored procs

---

False dichotomy. IMO, you can get the sweet spot of both by using raw SQL (which is an important skill, that we should not shy away from) with tools that simply make that convenient. Stored procedures introduce some coupling / deployment issues (timing, etc) that most people don't need; ORMs can be poor performing. Most people aren't going to change RDBMS (and if they do, it will be a major project anyway), so: I'm a big fan of parameterized command text. And tools that help, for example, dapper.

Another user posted a LINQ example of filtering by name; well, here's the same in dapper:

string name = ...
var list = connection.Query<YourType>(
    "select * from TableName where Name=@name",
    new { name }).ToList();

Fully parameterized; havily optimized; safe; quick; clean; easy to get right. Job done.

---

A lot has been said already in response to the original question. My answer is based on commercial aspects and not technical. Here is my experience, perhaps it will give some pointers to some who are starting new projects. We started with Asp.net + SQL Server and the application grew into a complicated one using about 800 stored procedures. For three years we go free SQL server through a Microsoft program so never cared early on. When the licensing quote of over 50K hit the table (its based on number of cores/threads and you also need a separate license for slave server), we ran to migrate to MySQL. Wish we were not using stored procedures. Without stored procedures, we could easily migrate quickly, within a few days. Took us much longer and great efforts to rewrite SPs into MySQL. If you are not using SPs, your app is more flexible/portable and it is easier to debug code within your app itself.

So if not technical, from commercial viewpoint there is a strong case against using stored procedures in web applications. If not using SPs, you may also distribute your app to customers with db schema (which will include tables only) while your application logic is still hidden in Linq+EF or Linq SQL.

---

My preference is LINQ, checking the queries produced in a tool like EF Profiler. If any generated queries are too long or create N+1s, they can normally be fixed. Once in production I keep my eye on the stats and if a slow query can't be fixed by caching I will move it to a stored procedure.

The main advantages to doing your queries in linq are:

• Easy to source control and deploy query changes
• Queries are compiled so if you change/rename something you know if you've broken something
• Can unit test your queries (there are probably ways to do this with stored procs, but I've not seen anyone do it)
• Can easily track down where queries are used and remove ones no longer required

When I've joined any project that has a lot of stored procs there are often a lot of them that are not used (or probably not used), but no one dares remove them as no one is sure.

The only time I would start with stored procedures would be if I was working in a corporate environment where access to the database was restricted in such a way that I had to.

---

One more thing to add: Using TSQL you can create a pack of queries to be executed as a unit to make sure that the access to the rows is locked till you finish your list of queries. As long as I know I think it could not be done using LINQ.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

This is very useful - for example - when you don't want a new insert to be executed while you executing a list of queries.