Which is better for development, Stored Procedures or Entity Framework for modern web development.
I will compare these frameworks using the following criteria.
- Business Logic
- Code Discovery
- Amount of Code
SQL is a language that is was written to allow processing of sets of data. e.g. Update all the records in the table to today’s date. This is great if you have massive sets of data that need processing. Thing is most LOB(Line of Business) apps only tend to update 1 row of data in a table. So the full power of Stored Procedures is rarely used.
SQL Server can pre-compile Stored Procedure for performance. When Entity Framework(EF) sends a request via a parameterized query, this is also complied and stored by SQL Server. This results in both Stored Procedures having the same performance for queries.
Also if it is just as easy to write badly performing Stored Procedures as EF Queries. In the past I have managed to speed up both SPs and EF Queries by 100x to 1000x.
I have seen a lot of dynamic sql code in Stored Procedures, since Stored Procedures are hard to write. Problem is as soon as you use dynamic sql code in SP’s this is slower since SQL server now as to compile this query each time.
Both can perform well, but only if you understand SQL Server and SQL queries really well, as the same SQL mistakes can make either method slow.
SQL server provides a comprehensive security system but I have yet to see a web system that doesn’t use a single login to access the SQL server which renders most of the SQL security inactive.
One issue that I have come across recently is the use of Dynamic SQL in stored procedures, which introduces two issues, the first been performance since SQL can no longer use a precompiled version and the second being that the code is now vulnerable to SQL Injection Attacks! The dynamic code was used since the programmer had difficultly getting SQL to do what they required and the dynamic code was the easiest workaround. Unfortunately this also introduced major security problems.
I am working on a number of applications at the moment, one based on EF and the other SP’s.
With the EF app the business logic resides in the C# code, which makes it trivial to find and navigate all the relevance pieces of code. Visual Studio and ReSharper both provide excellent tools for searching code.
The app using SP’s has business logic randomly distributed through the SP’s and C# code. Making it next to impossible to work out what is happening let alone make safe changes to the code.
Using EF and C# is definitely the way to go.
Say you wanted to find all the bits of code that updated a field in your database. I simple task that you often have to do when working out how some code works.
Using C#and EF with Visual Studio and Code Lens, you can quickly look at all the places it is used.
With the code base using SP’s, it’s just not possible in any simple fashion that isn’t massively time consuming. You have to use a global search for the field with the major problem being that the search will return maybe 100’s of results that have nothing to do with what you are looking for.
EF and C# wins this hands down, it’s simply no contest.
I have had this issue recently on a some different projects.
On the one that was primarily stored procedures it was a nightmare. Since the SP’s where in SQL server and the access code was stored in Visual Studio Projects. The only way to find all the field to rename was to do a global find and replace that took ages. Even when I finished I wasn’t 100% sure I had got everything.
On the code base that had was EF based, I used the “Rename” refactor and created an new migration. The job was done in 5 mins.
Once again EF and C# wins this hands down.
Amount of Code
This one is always important, as the lower the lines of code you have the lower the chance of a bug.
When using EF , it only take a few lines of code to do a query. 1 to open the DB connection and another to do the query.
This Stored Procedure there is a stack of scaffolding code you need, to open the connection, set up the SQL command, pass the parameters in, and then execute the command. Then if you have a DAL you will need code to convert the result into objects. Then you also need to write the Stored Procedure as well.
EF and C# wins this easily as well, less code is less bugs and the less code you have to write the more productive you will be.
When if comes time to scale your app, the less work SQL Server has to the better the chances of you scaling your app.
If all the work done via Stored Procedures and the SQL CPU is close to 100% most of the time, you are going to have issues very quickly. Same if the SQL Server’s disk IO is close to 100%.
With higher loads you want to shift more and more of the work to the web and application servers and away from the SQL server box. After all 100 or even 10 web servers are going to have more network, CPU and Memory than a single SQL Server.
With EF since all the business logic is already in code, you now have the ability to add caching layers and other techniques to remove the load from SQL Server.
With Stored Procedures since your business logic is in them, your opperiatunes to reduce load are limited until you can move the business logic to code.
I going to give this round to EF and C# as well.
For any new project you should consider using an ORM like EF. The gains it will give you in productivity will outway any perceived shortcomings.
If EF doesn’t quite meet your needs there are a stack of other ORM’s out there. Like Hibernate etc. There is sure to be one that will work for you.
On a side note, most No-SQL databases have no concept of Stored Procedures since they don’t really make a difference when scaling an application and they introduce a lot of unnecessarily complexity.