Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
A person I used to work with several years ago was hired to rewrite a product using a .NET-based modern tech stack. One of the aspects that stroke me was that he believes that the product should mo...
#4: Nominated for promotion
by
Alexei
·
2022-02-13T11:35:57Z (almost 3 years ago)
#3: Post edited
by
Alexei
·
2022-01-30T11:38:55Z (almost 3 years ago)
simplified the title
What are cases when stored procedures are preferred over application layer code?
- When stored procedures are preferred over application layer code?
A person I used to work with several years ago was hired to rewrite a product using a .NET-based modern tech stack. One of the aspects that stroke me was that he believes that the product should mostly rely on stored procedures and less on the ORM (e.g. Entity Framework). I have also heard this argument coming from a couple of formerly technical managers where I work.- The main argument is the performance and I think this is due to the inefficiency of handling batch operations of the EF .NET which have mostly been solved in the latest EF (.NET Core) versions.
- This [SO answer](https://stackoverflow.com/a/9483781/2780791) provides a lot of insight why it is bad to use stored procedures: harder to maintain (debugging, configuration, deployment etc.), DRY violation, rigidity, harder to test.
- Performance-wise, from my experience, correctly implemented applications should almost never rely on stored procedures since you almost never serve a lot of data for your clients (i.e. use pagination for both lists, grids and API results).
- The only valid case for the stored procedure was heavy computations (i.e. involving a high volume of data) on the database that could not possibly be justified by the DB - web roundtrips incurred by implementing that logic.
- I am wondering if there are any other cases when one should consider using stored procedures.
- []()A person I used to work with several years ago was hired to rewrite a product using a .NET-based modern tech stack. One of the aspects that stroke me was that he believes that the product should mostly rely on stored procedures and less on the ORM (e.g. Entity Framework). I have also heard this argument coming from a couple of formerly technical managers where I work.
- The main argument is the performance and I think this is due to the inefficiency of handling batch operations of the EF .NET which have mostly been solved in the latest EF (.NET Core) versions.
- This [SO answer](https://stackoverflow.com/a/9483781/2780791) provides a lot of insight why it is bad to use stored procedures: harder to maintain (debugging, configuration, deployment etc.), DRY violation, rigidity, harder to test.
- Performance-wise, from my experience, correctly implemented applications should almost never rely on stored procedures since you almost never serve a lot of data for your clients (i.e. use pagination for both lists, grids and API results).
- The only valid case for the stored procedure was heavy computations (i.e. involving a high volume of data) on the database that could not possibly be justified by the DB - web roundtrips incurred by implementing that logic.
- I am wondering if there are any other cases when one should consider using stored procedures.
#2: Post edited
by
Alexei
·
2022-01-30T08:49:44Z (almost 3 years ago)
fixed the title
What are cases when stored procedures are proffered over relying on application layer code?
- What are cases when stored procedures are preferred over application layer code?
#1: Initial revision
by
Alexei
·
2022-01-30T08:44:21Z (almost 3 years ago)
What are cases when stored procedures are proffered over relying on application layer code?
A person I used to work with several years ago was hired to rewrite a product using a .NET-based modern tech stack. One of the aspects that stroke me was that he believes that the product should mostly rely on stored procedures and less on the ORM (e.g. Entity Framework). I have also heard this argument coming from a couple of formerly technical managers where I work. The main argument is the performance and I think this is due to the inefficiency of handling batch operations of the EF .NET which have mostly been solved in the latest EF (.NET Core) versions. This [SO answer](https://stackoverflow.com/a/9483781/2780791) provides a lot of insight why it is bad to use stored procedures: harder to maintain (debugging, configuration, deployment etc.), DRY violation, rigidity, harder to test. Performance-wise, from my experience, correctly implemented applications should almost never rely on stored procedures since you almost never serve a lot of data for your clients (i.e. use pagination for both lists, grids and API results). The only valid case for the stored procedure was heavy computations (i.e. involving a high volume of data) on the database that could not possibly be justified by the DB - web roundtrips incurred by implementing that logic. I am wondering if there are any other cases when one should consider using stored procedures.