Could you explain more? Almost everywhere I’ve worked from Fortune 250 on down has used stored procedures with applications and it seems extremely clean and performance-oriented.
If anything, it separates code from the data more as far as I can tell, so maybe I’m missing something?
Also, if something is somewhat data driven and there’s a bug, you simply alter a procedure versus doing a build and deploy of the entire application.
If anything, it separates code from the data more as far as I can tell, so maybe I’m missing something?
Stored procedures are code – so you’re putting code in the database. How do you test that code? How do you source control that code? How do you roll back that code to the previous version or compare it to a previous version? How to know the history of that code? If that procedure is designed to work in together with application changes, how to test and deploy those together? This is all not impossible but it’s certainly more difficult and creates more potential failure points.
Also, if something is somewhat data driven and there’s a bug, you simply alter a procedure versus doing a build and deploy of the entire application.
That’s the problem. You write like that like it’s an advantage but you’re literally editing code live in production.
The performance advantages of stored procedures are unsupported. Most database engines do not treat stored procedures any differently than regular queries. And it’s not that stored procedures aren’t optimized, it’s that queries are equally optimized.
Fortune 250 on down has used stored procedures with applications and it seems extremely clean and performance-oriented.
A lot of these companies also still use COBOL on mainframes (something I’ve actually worked on and don’t recommend either). Stored procedures made a lot more sense historically when SQL might actually have more expressive power than your programming language and when database interfaces were much complicated and non-standard.
You are not logged in. However you can subscribe from another Fediverse account, for example Lemmy or Mastodon. To do this, paste the following into the search field of your instance: !programming@programming.dev
Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!
Cross posting is strongly encouraged in the instance. If you feel your post or another person’s post makes sense in another community cross post into it.
Hope you enjoy the instance!
Rules
Rules
Follow the programming.dev instance rules
Keep content related to programming in some way
If you’re posting long videos try to add in some form of tldr for those who don’t want to watch videos
This my hot take: Do not use stored procedures with applications. Keep your data separate from your code.
Could you explain more? Almost everywhere I’ve worked from Fortune 250 on down has used stored procedures with applications and it seems extremely clean and performance-oriented.
If anything, it separates code from the data more as far as I can tell, so maybe I’m missing something?
Also, if something is somewhat data driven and there’s a bug, you simply alter a procedure versus doing a build and deploy of the entire application.
Stored procedures are code – so you’re putting code in the database. How do you test that code? How do you source control that code? How do you roll back that code to the previous version or compare it to a previous version? How to know the history of that code? If that procedure is designed to work in together with application changes, how to test and deploy those together? This is all not impossible but it’s certainly more difficult and creates more potential failure points.
That’s the problem. You write like that like it’s an advantage but you’re literally editing code live in production.
The performance advantages of stored procedures are unsupported. Most database engines do not treat stored procedures any differently than regular queries. And it’s not that stored procedures aren’t optimized, it’s that queries are equally optimized.
A lot of these companies also still use COBOL on mainframes (something I’ve actually worked on and don’t recommend either). Stored procedures made a lot more sense historically when SQL might actually have more expressive power than your programming language and when database interfaces were much complicated and non-standard.
Whats your preferred method of accessing sql, in that case? Most companies I’ve worked for use sprocs. ORM?
Direct queries and ORM. A stored procedure doesn’t provide much value as a unit of abstraction that couldn’t just exist in the code.
☝️He’s right you know