Is Entity Framework Core slower than using ADO .NET and stored procedures?

Question

I'm looking at designing some new software for work, and I have been using EF Core quite a bit in my personal projects.

In my mind, EF Core generates queries for the underlying data provider which returns data. These queries are ad-hoc, or the same as running a query on the MSSQL database through SSMS or equivalent.

I know with stored procedures we get stored execution plans so stored procedure's execute faster next time, as the plan doesn't need to get regenerated again. Therefore, for an enterprise system, EF Core would quite a bit slower for data operations than using, say, something like Dapper and Stored Procedures.

Do I have this completely wrong? Does EF Core on MSSQL somehow cache its execution plans and thus make the performance difference not as huge? Or do I have this entire thing back to front?

Answer 1

I know with Stored Procedures we get stored execution plans so stored procedure's execute faster next time, as the plan doesn't need to get regenerated again.

That's not really true, nowadays. What you said was true 15?-25 years ago, but RDBMSs are pretty sophisticated softwares these days. They can detect when you're sending a query you've already run before and can reuse execution plans. If you think about it, it should be easy to think up how that could be accomplished with paramaterized queries, and so it wouldn't even be a big mental leap to think about how it could be done even with values right in the query.

So, nowadays, stored procedures have virtually no benefit with regards to performance over regular SQL queries. They can still be useful for certain situations, where queries have convoluted permissions associated with them, and a few other uncommon situations.

Most of the old benefits of SPs you can get now with just straight SQL, which EF supports handwriting queries for cases where you need it.

Answer 2

It depends!

Stored procedures are not inherently faster since SQL Server also caches execution plans for regular queries, even if they are generated ad-hoc by EF or some other mechanism (including through SSMS).

But stored procedures may be more performant in cases where they contain multiple queries or temporary results since data doesn't need to go back and forth between client and server. So stored procedures with multiple steps, temporary tables or conditionals are probably faster then the equivalent EF code.

EF has other advantages though since it performs caching of entities on the client side. So you cant really say either approach is definitely faster. It depends on the application.

Answer 3

Test it out. Write your code using EFCore, normally it works fairly well. Be careful with how you structure the Linq queries and get used to it’s join syntax.

If it’s slow make sure your only requesting the data you need; Select queries to anon objects let you grab only the columns needed.

If you don’t need to save back to the database run without Entity tracking.

If all else fails and your convinced EF is slow you can still write raw SQL or call stored procedures; it just means you business logic gets spread across layers of your stack and testing will be harder.

Answer 4

Do I have this completely wrong?

Yes. Stored Procs are not faster than 'normal' sql queries.

Where you might find a performance problem with EF core vs SqlClient is if EF generates an inefficient query. Which can occasionally happen where you use the EF objects for multiple purposes or have complex logic to perform as part of the query.

Hardcoding your queries gives you better visibility over their change control and performance.

Obviously on top of that you have the deserializing to objects, but you are unlikely to get this much faster via any other method.