Entity Framework Vs Stored Procedures - Performance Measure
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:
- look up a user's authentication (is the login still valid?)
- look up permissions (can they purchase said items?, are there special requirements?)
- look up stock quantities to make sure they were not depleted in process
- write to DB to reserve (remove from available inventory) items before payment
- look up payment info
- 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.
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.