2

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?

  • 1
    You should run some tests yourself to compare performance in your environment. I bet you'd be surprised at how small a performance difference you'll find. – Eric King Apr 3 at 23:58
  • “Quite a bit slower” is probably overstating things. And regardless, that time is tiny compared to actually fetching and transmitting the data. – Telastyn Apr 4 at 0:36
  • Its good to know that I was overstating things. Are you able to shed any light in relation to whether the queries that EF Core run benefit from any sort of cached execution plan like stored procedures are? – Lewis Cianci Apr 4 at 1:10
  • @LewisCianci, you always can use a query to retrieve compiled query plans. After executing EF query you will be able to check was new execution plan compiled or already existed used. – Fabio Apr 7 at 0:57
  • In some cases, you don't just get some data back from the query, you want to build a "program" that validates/queries data across different tables, inserts some tables and update some other tables and log data on the server. This kind of processing "should" be run on the server. In general, if you have N interactions with a rdbms or a file system that does not involve client intervention, it should be all executed on the server so that to minimize communication between client and server. Now, if you use RDBMS, you are bound to use SQL anyway, so what does EF buy you exactly? – NoChance Apr 8 at 19:12
3

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.

0

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.

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.