4

I am new to ef and liking it since it reduces the overhead of writing common queries by replacing it with simple add, remove functions. Agreed.

Today I got into the argument with my colleague who has been using it for a while and approached him for advice on when to use Stored Procedures and when to use EF and for what?

He replied;

Look, the simple thing is that you can use both but what's the point of using an ORM if you are doing it all in database i.e. stored procedures. So, how would you figure out what to do where and why? A simple formula that I have learned that use ORM for all CRUD operation and queries that require 3-4 joins but anything beyond that you better use stored procedures.

I thought, analyzed and replied;

Well, this isn't the case, I have seen blogs and examples where people are doing massive things with EF and didn't need to write any procedure.

But he's stubborn and calling it performance overhead which is yet beyond my understanding since I am relatively new as compared to him.

So, my question is that whether you only need to handle CRUD in ef or should do a lot more in EF as a replacement of stored procedures.

| improve this question | |
  • see How do I explain ${something} to ${someone}? – gnat Jan 18 '19 at 11:14
  • @gnat: man, that's not the problem, the problem is more specific. What my questions are actually have been updated, read the last para. – azure boy Jan 18 '19 at 11:18
  • 1
    Nobody can answer this definitevely because it's just opinion. There are as many different ways of combining ORMs and DB native operations as there are people to do it, and none of them is definitively "right" or "wrong". There are good (and bad) reasons for doing both ways, it totally depends on the situation. – MarcE Jan 18 '19 at 11:23
  • @MarcE: if you could elaborate few scenarios? – azure boy Jan 18 '19 at 11:25
  • 5
    I can't, it's just too broad. You could say "never use an ORM, they're slow". You could say "the problem isn't complex enough to care about slow, ORMs are simpler". You could say "carefully examine the performance every query and only write SPs for ones that are too slow". You could pick and choose depending on some set of rules (>4 joins or whatever). You could say "this table has 10 million rows, don't touch it with an ORM". Everything is a choice that needs to be made depending on the situation and at some point you just have to try it and see what happens – MarcE Jan 18 '19 at 11:31
2

I tend to take your approach on the matter. ORMs are great for basic CRUD and relatively simple queries, but often times I prefer to go to the database directly, usually for performance reasons.

I can write a complex view (or sproc) in SQL Server that can be optimized much more easily than the same can in Linq. On the other hand, I think it's correct also to be wary of putting actual logic inside your database.

ORMs are one tool for interacting with a database, and for day to day things, they can simplify enormously as opposed to writing SQL statements. But, as is the case with just about every other tool, it's not a silver bullet to completely replace DB interaction.

I tend to have two rules I follow here:

  1. Attempt to only use direct DB objects for performance optimization reasons, NOT for calculation of business logic (sometimes the two cross paths, but often you can generate base views/sprocs as a source for a .NET method that will then perform the actual logical set comparisons), and...
  2. Use your DBContext class/repository class to call the views/sprocs you may have created for these optimization purposes. That is, if you have a DBContext class to get your standard EF DBSets, you should also expose and consume your calls to the "supporting objects" there (so, sproc dbo.GenerateReportSet, for example, could be called like _context.GenerateReportSet(dateStart, dateEnd)
| improve this answer | |
2

But he's stubborn and calling it performance overhead which is yet beyond my understanding

It's hard to have a detailed discussion about something when you don't understand (and thus are unable to acknowledge) part of the problema outset.

Very simply put: automation can only be so clever. When dealing with complex and intricate queries, EF will generate a working query, but it won't be as efficient as you want it to be.
EF isn't badly developed. The generated inefficient queries are just an inevitable consequence of what EF is (and isn't) built to do. EF is a great tool that avoid trivial and repetitive code; but those benefits are lost when you're dealing with highly customized (generally complex) queries.

Think of it this way: my sedan is built for road use. On the road, it will be much easier to drive than a large 4x4. Can I take my car offroad? Within reason, yes. But my car wasn't built for offroading, and thus my car's benefits are lost when not on the road, whereas the 4x4 starts to shine and will be easier to drive offroad.
Like my car is built for road use, EF is built for simple CRUD tasks. It can be used in other ways but no one every designed it with that in mind.

"Look, the simple thing is that you can use both but what's the point of using an ORM if you are doing it all in database i.e. stored procedures."

That argument makes little sense. What's the point of using a toothbrush if your arm moves the toothbrush anyway. Just use your arm to clean your teeth!

Well, this isn't the case, I have seen blogs and examples where people are doing massive things with EF and didn't need to write any procedure.

Just because it can be done does not mean that it is being done well. I've seen people eat the food I made but that doesn't mean I'm a good cook. It just means I'm not fully incapable of cooking.

EF can do many things, but its efficiency starts falling apart as the complexity of the query increases. In cases where the subsequent performance hit is unacceptable, EF is not the tool to use.

So, my question is that whether you only need to handle CRUD in ef or should do a lot more in EF as a replacement of stored procedures.

Like many things, it depends on what you need.

For simple CRUD operations, EF is a good tool to use. It removes the need for a lot of boilerplated handling logic.

If you only have simple data queries, or don't care about optimizing performance, then you can use EF for those since you're already using EF for CRUD anyway.

However, if you have intricate data queries and care about the performance optimization, then you won't want to use EF. Our lead architect (who used to be a DBA and very much cares about DB performance) currently advocates for using EF and Dapper simultaneously, specifically because they each specialize in part of the workload. He initially suggested only using Dapper, but he has conceded the ease of CRUD with EF and accepts its usage in CRUD-heavy applications.

But again this is subjective. You might prefer to stick to one tool and suck up the drawbacks (e.g. use EF and deal with slow complex queries; or use Dapper and deal with having to write boilerplate CRUD code). Or you may be in favor of using different tools so that you can squeeze performance at all times.

| improve this answer | |
  • We use a similar combo at my current workplace - EF + a homemade ORM solution that is customized for specific needs on the more performatic parts. It works wonders - the correct tool for the correct job. – T. Sar Feb 13 '19 at 10:15
2

As always, it depends. Stored procedures, views, indexes and triggers (etc) are great tools for a DBA, making sure that the (relational) database itself guarantees consistence, secures proper permissions and performs as well as possible.

However, those tools are a sure path towards a database-centric design, where the whole system revolves around the database. This means that all code will have to adapt to the physical database model. The data types used by the database will dictate in-memory types etc. The reason for this is that it will be very hard to initiate a refactoring of the database layer from your business logic code if a recactoring includes rewriting (and testing/debugging) stored procedures, triggers and custom SQL code.

The main benefit of EF (and similar tools) is not (IMHO) that they make simple CRUD components easy to develop, but that subsequent refactoring of those components becomes much easier. (Given that you do code-first)

Yes, there is a performance price to pay once you start to do complex joins. That price is much higher if you have a naive approach to EF.

However

If you run into real (as in end user actually suffers) performance issues because of sql queries containing may complex joins, then stored procedures are a band-aid at best.

You can optimize storage for data integrity or for query performance, but not both. If you need both, look into CQRS or similar patterns.

TL;DR

  • EF makes writing and refactoring data access code faster, which is good.
  • The price for this is non-optimal queries (worse if you are not experienced with EF)
  • Stored procedures and other DBA techniques can improve DB performance, which is good.
  • The price for this is that business code will be harder to write and refactor.
  • If query performance is an issue (given competent EF use), reevaluate overall design rather than trying to optimize SQL.
| improve this answer | |
1

Just to add to what @Guran has answered: I don’t have experience with EF specifically, but I have seen that it’s easy to forget that you are dealing with joins and expensive operations when you are using an ORM. So you start treating model instances as regular variables and include them in complex operations which in turn will probably be turned into even more complex sql queries. I have also seen client side code with loops and aggregations that could have been done more efficiently in the database server.

So if you hand write sql/stores procedures you will be more aware about the performance implications.

| improve this answer | |
0

In most cases where a sproc is needed for some requirement. Place a sql script file in the codebase to execute in the case of future business rule refactoring needs. I would assume most new and experienced developers have some experience with working in a dbms. Anyone needing to know the DB sproc requirements can review and update the the scripts as needed. Business rules and DB requirements exist in the source code files. I to believe there is no one way method in most complex solutions. (JMO).

| improve this answer | |
-1

You are thinking from the database perspective. All your logic is placed around or inside the database. This is called database-driven design.

Let me explain the idea.

Databases are just details, that should be responsible for storing and returning data. That is the main purpose of any data store. When you are applying DBMS specific features like Stored procedures, Triggers, etc. You are just moving you business logic inside the database.

I have seen this approach in a lot of legacy systems, where almos all business rules are defined inside stored procuderes. I don't suggest to use stored procedures at all, only for specific cases.

Consider a situation when you decided to move to another DBMS and it doesn't support Stored Procuders, or they are defined in completely other way, you have to spend a lot of time for this migration. Furtermore, it can be really tricky sometimes to debug stored procedures. Or you can get unpredictable behavior of your system when something went wrong inside a stored procedure.

Entity framework is a great tool, it allows you to define your models without defining a database schema. This is called the Model-First approach. That is exactly the idea of decoupling your business rules from details like databases. You defined a Domain Model that fits for your business needs without thinking about infrastructure details.

| improve this answer | |
  • 1
    I agree with you, that you shouldn't do data-driven design, if you want a maintainable system. But you also kind-of fall into the other extreme, which is to just use database as a dumb data storage and retrieval system. I think this position is also suboptimal in most cases. Databases (whether sql or nosql) have a lot of specific and non-replaceable features nowadays. It's not wrong to use those features and make the database a (potentially not easily replaceable) part of your system. – Robert Bräutigam Jan 19 '19 at 10:09
  • 1
    I'm not sure I can think of any modern RDBMS which doesn't support stored procedures. Realistically speaking, I'm not sure how this would be a problem - surely it would merely be a constraint that the new RDBMS would simply need to be one of the many modern solutions which does support stored procs, and the most likely reason to move these days is a migration from on-premise up to the cloud, in which case it's no problem because all the main cloud offerings are underpinned by something like SQL server or MySQL. – Ben Cottrell Jan 19 '19 at 10:13
  • @BenCottrell, you are absolutely right, but any decision to put business logic inside database should be considered and discussed several times. There is nothing wrong to use DBMS features for storing, retrieving,searching, managing data, but core business rules should be implemented in code. – CROSP Jan 19 '19 at 22:14
  • 1
    You talk about moving to another DBMS as if that was common or happened often. In my experience, this never happens. What actually happens is that the database stays the same but the application changes, or that both are replaced at the same time. – Christian Hackl Feb 13 '19 at 9:45
-2

Your colleague is essentially correct. But perhaps set in their ways.

At the end of the day EF is a fancy way to generate SQL commands. You trust that its syntax and generation methods allow you to express complex queries and generate efficient SQL.

But if you can just write that efficient SQL yourself. You can skip EF.

This is particularly true for spocs, as once you start using them you have to abandon the EF query syntax and just run the sproc. You might as well use SqlClient.

However!!

You can take it a step further and not use sprocs either. Just hard code your SQL. The arguement that sprocs run faster isnt really true for parameterised SQL as it will be compiled on the first run.

Use this is to test your colleague. Do they really want to streamline their code, or are they just used to making sprocs and sticking to what they know.

Personally I do not use EF or sprocs and find sticking to SqlClient repositories makes for highly streamlined, organised and efficient code.

| improve this answer | |
  • 1
    EF is more than just a tool to create SQL queries. If that's all it was, no one would use it. It also is a tool that handles the mapping between SQL rows and programming data objects. Using EF is handy because writing all that data mapping is tedious and boring. – whatsisname Jan 21 '19 at 3:33
  • But if you can just write that efficient SQL yourself. You can skip EF. The ability to do so does not mean that having to do so is the best approach. If I really needed to, I could walk the 50km it takes me to get to work. But it's much better to use transportation as it dramatically cuts down on the time and effort needed to get to my goal. The suggestion that hardcoding the SQL commands as opposed to using EF is not just nonsensical, it is also going to lead to a bugfest that the compiler cannot catch when changes are made to the codebase. – Flater Feb 13 '19 at 7:45
  • so say someone whos never used sprocs – Ewan Feb 13 '19 at 8:05
  • I assume that snide remark was targeted at me. Sprocs are performant but they have other drawbacks. The main argument against stored procs is that they are separated business logic and split the codebase into different pieces in different locations, and often end up being unversioned. Additionally, not every developer is a DBA, sprocs require a bit more effort to handle in code, and not every application needs the performance gains that sprocs bring to the table. As was said in other comments/answers here: the right tool for the right job. And sprocs are not the be-all-end-all solution. – Flater Feb 13 '19 at 10:19
  • The point is that regardless of whether they are a good idea or not they have a massive effect on how you implement EF. EF seems nice and easy as long as you have only worked on small or green field solutions, because you never hit the complexity it introduces. Once you do, the skip EF agrument will make sense – Ewan Feb 13 '19 at 11:38

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