[btilly]

Scalability IS NOT performance.

I like to say that scalability is like a Mac truck. Good if you have to move a lot of stuff, but not necessarily the best tool for getting your groceries. Moving logic from stored procedures to the application adds latency and network traffic. This is never going to be good for how fast you process an individual request. However it can let your system handle more requests per second.

[Jweb_Guru]

That's only true if there aren't resources taken up by your in-flight transactions, and if those resources don't exceed the cost of just running the logic in-place. Otherwise, you're not sacrificing latency for throughput, you're just sacrificing latency and throughput. Unless you have a very heavy application-level thing to do that doesn't require periodic new access to data, that's usually not the case, especially if you're trying to process requests at line rate (NIC queues will get exhausted just trying to hold all the pending requests in memory, and you'll start to suffer from lock manager contention, page buffer swapping, etc.).

[btilly]

True.

But my experience with Oracle specifically is that handling connections is just general overhead, while the real failure modes have to do with overloading random latches somewhere deep inside of the system. I also found that logic in stored procedures caused us to hit limits faster than leaving it in the application.

The specific case I saw this in was a simple set of queries to test if you were in an A/B test, and if not to assign you to a random variant. After I left my application logic was moved into a complex stored procedure, and they then had scalability limitations that they didn't have before. For political reasons they declared success and ran fewer A/B tests...

(I've used a lot of other databases as well, but Oracle is the only one I've really pushed to its scalability limits.)