5 ms·
I've worked on a service like this. 800k lines of PL/SQL and Java stored procesures (running inside the database, so you could call Java from PL/SQL and vice ve
by zerd 7y ago
I've worked on a service like this. 800k lines of PL/SQL and Java stored procesures (running inside the database, so you could call Java from PL/SQL and vice versa), powered by triggers.
* Testing is god-awful. To test a simple thing you had to know how the whole application worked, because there's validation in triggers, which triggers other triggers, which require things to be in a certain state. This made refactoring really hard/risky so it rarely got done.
* There's a performance ceiling, and when you hit that, you're done. We did hit a ceiling, did months of performance tuning, then upgraded to the biggest available box at the time, 96 cores, 2TB ram, which helped, but next time the upgrade won't be big enough. You're limited in what one box can do (and due to stored procedures being tied to the transaction there's limits to what you can do concurrently as well)
- projektfu 7y agoI think they go too crazy with the stored procedures. I would rather see constraints and views to make the data work. Triggers are useful for simple behind-the-scenes things like logging, or creating updateable views, but their behavior should be kept simple. If they're sending e-mails and launching missiles, it's probably too much. Debugging PL/SQL without the PL/SQL debugger is nearly impossible. Unfortunately a lot of shops cheap out on developer tools after they buy the server licenses. I never liked the idea of Java on the database. The good thing about PL/SQL is that nobody wants to write it so it has a tendency to not be overused by most developers. The performance ceiling probably wouldn't be too low if there weren't too much extra activity with each update. As with all databases, sharding and replication are your friend.