19

If I have all my business logic in code and make use of Entity Framework, in what situations (if any) would I be better moving some business logic to a stored procedure, instead of keeping it all in code?

To be clear, I mean in conjunction with the current setup (business logic in code), not instead of. I have seen a number of similar questions that are asking for the pros and cons of having all business logic in stored procedures, but I haven't found much regarding using stored procedures sparingly for edge case logic, while keeping the rest of the business logic in code.

If it makes a difference, I am using MSSQL and Entity Framework.


These are the situations where I have used stored procedures before:

  • A complicated report that was taking minutes to run (this was a page in a web app). I found I could write SQL that was much more efficient (only taking seconds to run) than what LINQ was providing.
  • A web application needed to read and write to a few tables on a separate database which contained a lot of other, sensitive information that was irrelevant to the application. Rather than giving it access to everything, I used a stored procedure that only does what is needed, and only returns limited information. The web application could then be given access to this stored procedure only, without access to any tables etc.

Other posts I have looked at before asking this question:

  • 4
    Both of your situations are perfectly legitimate reasons for writing stored procedures. Are you asking why they are legitimate? – Robert Harvey Aug 17 '16 at 13:40
  • @RobertHarvey I was making sure they were legitimate and looking for other scenarios or reasons why you might make an exception and write a stored procedure rather than keeping it in code. – Amy Barrett Aug 17 '16 at 15:13
  • You would make an exception and write a stored procedure when you determine that it solves a problem better than the other techniques available to you, which is exactly what you did. – Robert Harvey Aug 17 '16 at 15:16
10

You have a couple of perfectly good scenarios already.

There are lots of other reasons too. EF is really good at CRUD and at pretty straight forward reporting. Sometimes, though, EF is not the perfect tool. Some other reasons (scenarios) to consider using stored procedures in combination with Entity Framework would include:

  • You have complex units of work, perhaps involving many tables, that cannot be wrapped in a transaction easily using the features of EF.
  • Your database does not play well with EF because it fails to take advantage of declarative referential integrity (foreign key constraints). This is usually a bad scenario to find yourself in, but there are sometimes appropriate scenarios, such as databases used for ETL processes.
  • You need to work with data that crosses server boundaries with linked servers.
  • You have very complex data retrieval scenarios where "bare metal" SQL is needed in order to ensure adequate performance. For example, you have complex joins that need query hints to work well in your specific situation.
  • Your application does not have full CRUD permissions on a table but your application can be allowed to run under a security context that your server trusts (application identity, rather than user identity, for example). This may come up in situations where DBAs restrict table access to stored procs only because it gives them more granular control over who can do what.

I'm sure there are many more besides these. The way to determine the best path forward in any particular circumstance is to use some common sense and to focus on the primary goal, which should be to write high quality, easily maintainable code. Pick the tool(s) that give you this result in each case.

  • Thanks Joel, you have mentioned a few other scenarios I hadn't thought of. – Amy Barrett Aug 18 '16 at 11:17
  • Nice Answer. However, I wonder if you have many users connected to the database and running complicated queries or stored procedures, won't you end up putting lots of load on the database server? – Ripal Barot Nov 28 '18 at 3:13
  • 1
    @RipalBarot There's no question that the more that's going on with workload (more users, more complex queries) the more will be the demands on your database server. One thing to keep in mind, however, is that using stored procedures rather than ORMs like Entity Framework may often lessen the workload (comparatively) because stored procedures have can have precompiled query plans. When a query comes from an ORM the DBMS often has to start with working out how to handle the query. When you call an equivalent stored procedure the database already knows how to fulfill it efficiently. – Joel Brown Nov 28 '18 at 6:35
2

Perhaps it makes sense, to turn the question around and find usecases where only stored procedures could do, what you want to achieve. Perhaps there are really use cases, where stored procedures stand out.

If it makes a difference, I am using MSSQL and Entity Framework.

My knowledge on EF is limited, but as far as I can see, EF is (just) an ORM like any other; and fortunately is capable of using raw SQL.

If I take your two major points:

A complicated report that was taking minutes to run (this was a page in a web app). I found I could write SQL that was much more efficient (only taking seconds to run) than what LINQ was providing.

LINQ / EF was falling short, when doing a report. And as you noticed, was SQL way faster, than using an ORM. But speaks this in favour of stored procedures or only against using the ORM for everything?

Your problem could obviously solved with SQL. If that query was stored and version controlled in your codebase makes - according to your example - at least no difference.

A web application needed to read and write to a few tables on a separate database which contained a lot of other, sensitive information that was irrelevant to the application. Rather than giving it access to everything, I used a stored procedure that only does what is needed, and only returns limited information. The web application could then be given access to this stored procedure only, without access to any tables etc.

Same thing here: a simple connection string and and UPDATE and your problem is done. This problem could be solved even with an ORM: simply use a webservice in front of the other DB and the same compartementalization / isolation is achieved.

So nothing to see here.

Looking at some points others have made:

You have complex units of work, perhaps involving many tables, that cannot be wrapped in a transaction easily using the features of EF.

But SQL can do that. No magic involved here.

Your database does not play well with EF

Again: Use EF when appropriate.

You need to work with data that crosses server boundaries with linked servers

I do not see, how stored procedures help. So I can not see an advantage of stored procedures; but perhaps someone sheds some light on that.

You have very complex data retrieval scenarios where "bare metal" SQL is needed in order to ensure adequate performance

Again: "Boundaries of EF".

Your application does not have full CRUD permissions on a table but your application can be allowed to run under a security context that your server trusts

Okay. I go with a maybe.

So far only a half point was made in favor of stored procedures.


Perhaps there are performance considerations, which speak in favour of stored procedures.

1) Storing queries has the benefit of a simple call to the stored procedure which encapsulates the complexity. Since the query planer knows the query, it is "easier" to optimize. But the saves are with the current sophisticated query planer _minimal.

On top of that, even if there is a slight cost using ad hoc queries, if your data is well structured and carefully indexed, the database is merely the bottleneck of your application. So even if there is a small delta, it is neglectable considering other factors.

2) Nevertheless it was argued for storing complex queries in a DB. There are two things to consider:

a) complex queries make massive use of the DB infrastructure, which increases asnwering times for every other query. You can not run many costly queries in parallel. This speaks neither pro nor contra stored procedures, but against complex queries.

b) If the query takes time anyways, why bother with small speed wins of a stored procedure.

tl;dr

Nothing speaks directly against stored procedures. So using stored procedures is okay - if it makes you happy.

But on the other hand: I couldn't imagine a proper use case which speaks unequivocally pro.

When should I use stored procedures?

The proper answer is: Whenever you like. But there are other options.

0

For your specific case, since you are using the entity framework, one should consider using stored procedures when the entity framework fails to meet a requirement or concern.

The entity framework does a decent job and performance will be close or equal to using stored procedures. You chose the entity framework because you did not want to be concerned with SQL and will let the framework generate the SQL for you.

But there may be an edge case where the entity framework falls short and cannot meet your needs. In this case one would write the SQL by hand using a stored procedure or parameterized query and then use the entity framework to call that stored procedure/SQL statement directly.

So, I wouldn't move anything to a stored procedure unless the framework that is being employed cannot meet the requirement.

I think the worst thing that can happen is that one choses to use the entity framework and then writes all stored procedures. Now one has an extra layer that adds no value.

  • 100% agree with last paragraph – Ewan Aug 17 '16 at 16:43
0

Having a technical need is not the most likely choice. Programmers prefer their tools and are usually more adapt at solving their problems with them. Putting logic in a sproc will be the exception. Technical debt and future devs having to take time to work with an exception should be considered. There may be a performance boost in your particular RDBMS that is just easier to implement in a sproc or maybe even another db object like an indexed view.

There will be times when you need data from a database and you just can't get it from your application code. In many large company/enterprise situations, business needs can exceed the IT Department's grasp. Companies get bought and sold and their applications come and go with them. Regulating agencies and banks don't care if you can't build your highly-scalable and beautifully coded app. They can make life difficult for the business, so you have to get it done.

I've run into situations where Third-party applications or report writing tools don't let you get at your code. No open source code, no API, no web interface and no services. The only thing they have is their own special report writing tool that only works with database objects: tables, views, sprocs, user-defined functions, etc. You put the logic wherever you can get at it.

If you have a DBA that is very good at writing stored procedures, you can leverage that talent in some situations. You may want to trigger a backup from your app because you're going to make a major data change, so why not use what your dba uses?

Some Ad hoc requests are just easier to write a proc until you can get all the functionality in your app. We hate it. We push back, but the show must go on.

0

I would strongly advice against placing any business logic in a stored procedure. However, there might be a case (you listed two fine examples) where it is preferable to place data access logic there.

Generally speaking, if you feel tempted to use SP's it is a sign (a code smell) hinting that your data model is not well suited to its needs.

Edit: When developers ask me about business/data access logic, I say that business logic is about how data behaves and interacts, while data access logic is about how data is stored and retrieved.

For exemple: In your domain model you might have the entities "Student" and "Teacher", both derived from "Person". (Not saying this is preferred, just an example). This can be stored in a relational databas as one, two or three tables. The choice depends on how many properties they share, and on read/write performance requirements.

In Business logic it should not matter how those entities are stored. (or wether they reside in a RDB at all). Data access logic is all about how they are physically stored.

So, regarding the original question: If a stored procedure makes storing and retrieving data more efficient (or more robust) you have a case. If the stored procedure is merely for imposing a rule that i valid regardless of how data is stored, avoid it. It makes your code more tightly coupled to the database.

  • 2
    How would you define business logic and data access logic? – Amy Barrett Aug 17 '16 at 15:15
  • @AmyBarrett: Business Logic, Data Access Layer. – Robert Harvey Aug 17 '16 at 15:32
  • @RobertHarvey Thanks for the links. Is data access layer the same as data access logic? I ask as there doesn't seem to be much actual logic involved in the data access layer - just simple CRUD operations. – Amy Barrett Aug 17 '16 at 15:55
  • @AmyBarrett: Well, sometimes you write custom methods in the Data Access layer, so it's not always CRUD. – Robert Harvey Aug 17 '16 at 16:21
  • @RobertHarvey Wouldn't those custom methods belong in the business logic layer though? – Amy Barrett Aug 17 '16 at 16:39
-4

I think there are three issues here.

  1. Business logic in SQL is bad. Even if it runs faster individualy, it doesnt scale.

  2. Tradiionally SPROCs should always be used for all data access as an abstraction layer. Enabling a DBA to introduce performance or other datalayer changes without affecting the consuming applications.

  3. EF does not play nice with sprocs. You will lose a lot of the 'good' features and productivity gains by switching away from the dynamic query generation of Linq.

So my overall advice would be to

  • Use SPROCs for all SQL queries,

  • Don't put business logic or any kind of loops in them,

  • Don't use EF.

  • Because all the SQL runs on the db server you can only scale with extra dbs rather than extra compute boxes. – Ewan Aug 17 '16 at 14:10
  • 1
    I see. But then point #1 is purely about a trade-off between two performance issues. But there will be times when the SQL solution is a clear win in terms of performance, so I don't see how it can be considered categorically "bad" on this basis. – user82096 Aug 17 '16 at 14:13
  • 1
    say you have a webservice which does a calculation. You can do it in code or in the sql. say the sql is faster in a head to head test of a single calculation. But when the service is maxed out cpu its very cheap and easy to add a second, third, fourth.. box running the webservice but hard and expensive to add a second replicated database – Ewan Aug 17 '16 at 14:29
  • 1
    that's an example of one particular scenario where SQL may be a bad design due to scalability. But even then it's not clear: it depends on the expected number of users, the relative cost of performing the calculation in the database compared to outside, and so on. Of course you are correct in some cases, I just don't think it's a universal rule. – user82096 Aug 17 '16 at 14:39
  • 3
    This is bad advice, overall. There are plenty of options for data access that have advantages over hand-coding stored procedures and perform just as well. You can run sprocs directly from EF; in fact, you can run any sql query directly from EF. Some business logic runs better on a database server, so you can't state categorically that it's forbidden there. Object relational mappers are an 80% tool; they were never meant to completely replace your data access processes. Use stored procedures for what they are meant to be used for: the 20% that an ORM doesn't do well. – Robert Harvey Aug 17 '16 at 15:34

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.