Entity Framework Vs Stored Procedures - Performance Measure

Solution 1:

There are some things you can do to optimize your query. Here on MSDN you can find a nice overview.

But to be honest, a stored procedure with manual mapping will always be faster in performance. But ask yourself, how important is performance? In most projects, development time is way more important then performance. What was harder to develop? The raw query with parsing or the Entity Framework query?

ORMs are not designed because they perform so much better than a hand written approach. We use them because development is so much easier!

If you write your application with the Entity Framework and hide all your queries behind a repository pattern you can develop real quick and then, when performance becomes an issue, measure your application to detect the bottleneck. Then maybe some of your queries need optimization and can be moved to stored procedures and manual mapping.

Solution 2:

In agreement with @Wouter de Kort ... Moreover, when you need to move to procedures, you can use EF in conjunction with procedures to assist migration from one to the other.

Moving to procedures will be faster in a typical application if you unify functionality into well designed procedures. i.e. Get as much work done in one sproc call as possible. For example, in a shopping cart MVC app when a user clicks the check-out button, you might use the ORM to something like:

1. look up a user's authentication (is the login still valid?)
2. look up permissions (can they purchase said items?, are there special requirements?)
3. look up stock quantities to make sure they were not depleted in process
4. write to DB to reserve (remove from available inventory) items before payment
5. look up payment info
6. logging ... ?

Or it may be completely different steps, but in any case, the point is, the MVC app will use an ORM to make multiple calls to the DB to get to the next step.

If all this logic is encapsulated in one well written sproc then there is just one call to the sproc and you're done. With the MVC-ORM route the data must be copied from the DB to the driver and delivered to ORM (usually over the net to a different host) and then read by the MVC app to make a simple decision then repeated until all steps are complete. In the case of using a sproc that encapsulates that check-out step, there is a lot less data copying and moving to be done, less network IO, less context switching etc.

Think of the MVC-ORM solution this way. Person "A" is knowledgeable of facts only and person "B" has all the savvy to make decisions with given facts which he does not poses. Person "B" emails "A" for facts. Based on the answer from "A", "B" might request a few more facts before making a decision. That's a lot of back and forth messaging.

If you have one person that has all facts and the knowledge to make decisions, you just need to ask one question and their brain will process everything internally to come up with an answer. No deliberation with another person is involved. Naturally it's going to be faster.

That's not to say it's necessarily better. Separating facts from decision means that these components are separately replaceable / testable however, if you are married to your MVC and your DB then that's a "non-issue".

On the other hand many MVC fans hate writing SQL so they consider putting any decision logic into SQL as a natural disaster. For such people it's imperative to have any logic written in the same language that the MVC uses because it speeds up development for them. In some cases this is also a "non-issue" since in the case of some RDMBS you can write sprocs in the same language as the one used by the MVC (examples: .Net - SQL Server sprocs can be written in C# and use .Net ; Postgresql functions (no sprocs) can be written in Perl, Python, PHP et. al) The advantage in this case is that you can have fast sprocs that encapsulate multiple steps in one call and you can use a programming language that you are already quick in coding in.

Solution 3:

I can think of 2 very good reasons why I would choose stored procedures over ORMs

1. The advantages of encapsulation are well documented. You wouldn't ever create a class and then expect users of that class to interact with the inner workings of the class. What if something changed; what if you changed a variable type? How would you know where all the code that accessed that variable was? Solution, use an interface. Your database is no different, it's an object, so give it an interface. For SQL Server databases, that means stored procedures.

2. Having worked as a DBA for over 20 years, I've lost count of the number of times developers have describe a problem with code that they wrote as "A database problem", and suggested "Talk to the DBA" as a solution. Now, I don't mind this. When I'm fixing your mistakes, and you're not fixing mine... well let's just say it's something I will definitely raise during performance review. But the least you can do is code in a fashion which allows me to fix your mistakes in the shortest time possible. That means, put your code in stored procedures. When stored procedures cause performance issues, that's bread and butter for any decent DBA. But when ORMs cause performance issues, that's a nightmare for anyone. At least give your DBA a fighting chance when he's trying to help you.

And if you can't code in stored procedures, or it really takes you that much longer to do so, then you might want to think about a change of career.

Solution 4:

It is important to note that

Starting with the .NET Framework 4.5, LINQ queries are cached automatically. However, you can still use compiled LINQ queries to reduce this cost in later executions and compiled queries can be more efficient than LINQ queries that are automatically cached.

From MSDN Compiled Queries (LINQ to Entities)