3 ms·
Exactly what bad things happen when you put logic in the database? At my current employer, most business logic is in stored procedures.
by andrewflnr 13y ago
Exactly what bad things happen when you put logic in the database? At my current employer, most business logic is in stored procedures.
- kryten 13y agoLots. Here you go: 1. CPU time goes up. When you need to scale up, your brick wall is a lot closer. 2. When you do need to scale up, moving it out of the database engine is very expensive and time consuming. 3. The languages are archaic, obtuse and inadequate for representing business logic and don't cover ALL concerns. How do you call a web service and do an update in a transaction for example? How do you send an email? 4. It's incredibly difficult to test as the entire thing is stateful. 5. Error handling and reporting is terrible. 6. You lose database portability. This is a big issue these days, particularly when you get whacked with major licensing model changes. 7. Versioning and deployment with the application is utterly painful. Etc etc...
- Shorel 13y agoI don't think most of these apply to JavaScript nowadays.
- andrewflnr 13y agoPersonally, I'm interested in the general answer.
- andrewflnr 13y agoHaha, well, we're running a small site on a huge server and we're not that into automated testing. We actually send email by inserting into a table that serves as a queue. SQL server already costs a fortune. Honestly, though, I've felt a lot of that already, but didn't really attribute it to stored procedures. Can you explain why stored procedures are faster than code in my app? It seems like they're both run in a fairly optimized VM, but I don't know much about the details. We're using C# at work, but if I was using something like Python I would be more tempted to move things into the db for performance reasons.
- kryten 13y agoIt depends entirely on round trips to the server and your use cases. Generally speaking though, if you keep your result set as narrow as possible, post process aggregates in code, keep your join count low and keep you round trips per request low then c# will destroy stored procedures.
- ironchef 13y agoThe usual culprits are scaling and versioning / config mgmt; however, anything can be misused (and there's no one size fits all). I've seen it done well once. Once.
- kryten 13y agoThat's one more time than me :)
- twistedpair 13y agoSo many times I've seen people picking up the pieces of a "We'll just do everything in SP's!" project architecture." Anti-pattern city. The typical answer to make it scale or maintain well is to just throw it out.