3 ms·

Look, this is just wrong. All the best-performing research databases use stored procedures because they perform much better than other approaches. Your bottle

Look, this is just wrong. All the best-performing research databases use stored procedures because they perform much better than other approaches. Your bottleneck when it comes to validating your data is, generally speaking, your database, so you're not going to scale better by moving the logic farther away from it (and introducing a bunch of round-trip latency) and this is empirically confirmed in benchmarks of stuff like TPC-C. It's a really tired argument, especially when virtually every aspect of stored procedures but their performance is irritating.

paulryanrogers 10y ago
IME, the win in the application layer is when you can have faster copies pulled from a cache or validate rules that don't relate to other records.
BurningFrog 10y ago
That stored procedures scale badly is different from if they perform badly. With application logic on separate stateless app servers, you can quickly spin up as many of those as you need when your site goes viral. If it's in your DB, you can't. If the stored procedures are fast or slow doesn't really enter into that equation. It's about the difference between 1 and N. I actually did work at a startup that got into serious trouble because much of their application logic was in the DB.
- Jweb_Guru 10y ago
  Were you serving something that had to live in the database, or were you serving cached copies that didn't actually need to be updated or read within a transaction? There's a crucial difference there. The moment you are required to access the database at all, the equation changes (which is usually true for data validation, inserts, updates or deletes, and constraint management). If all you're doing is reading static data, there's no need to touch the database more than once and you can do much better. Moreover, if all you need is causal or eventual consistency (for application-specific reasons), you can use databases that provide those semantics. In pretty much all cases, once you've chosen the correct database / semantics for your use case, you'll be better off from a performance perspective with stored procedures; 1:N doesn't really enter into it because databases can run with their own isolated state on multiple nodes just as well as applications can (indeed, there are some caching systems built around this observation)!

paulryanrogers 10y ago

IME, the win in the application layer is when you can have faster copies pulled from a cache or validate rules that don't relate to other records.

BurningFrog 10y ago

That stored procedures scale badly is different from if they perform badly. With application logic on separate stateless app servers, you can quickly spin up as many of those as you need when your site goes viral. If it's in your DB, you can't. If the stored procedures are fast or slow doesn't really enter into that equation. It's about the difference between 1 and N. I actually did work at a startup that got into serious trouble because much of their application logic was in the DB.

Jweb_Guru 10y ago
Were you serving something that had to live in the database, or were you serving cached copies that didn't actually need to be updated or read within a transaction? There's a crucial difference there. The moment you are required to access the database at all, the equation changes (which is usually true for data validation, inserts, updates or deletes, and constraint management). If all you're doing is reading static data, there's no need to touch the database more than once and you can do much better. Moreover, if all you need is causal or eventual consistency (for application-specific reasons), you can use databases that provide those semantics. In pretty much all cases, once you've chosen the correct database / semantics for your use case, you'll be better off from a performance perspective with stored procedures; 1:N doesn't really enter into it because databases can run with their own isolated state on multiple nodes just as well as applications can (indeed, there are some caching systems built around this observation)!