Re: Why are stored procedures looked on so negatively?

On Tue, Jul 23, 2013 at 06:55:56PM -0600, John Meyer wrote:
> are accessing your database at one time? And most importantly, what
> are you best at?

That is one of the most important questions, for sure, but there's a
close second that I'd suggest: what are the scaling properties?

For practical purposes, if you're going to do complicated data
validation and business logic in the application, you have any
significant degree of contention, and you need to write some data, the
use pattern is going to look something like this (A is application, D
is database):

A: get some data
D: here you go, optimistic lock value L
A: do some work
A: given this value, get some more data
D: here you go, optimistic lock value L2
A: INS/UPD/DEL data, optimistic lock value L, RETURNING data
D: ok, here you go, optimistic lock value L3
A: do some work
A: INS/UPD/DEL data, optimistic lock value L3
D: ok

And that's if none of the optimistic locks fails. That's a lot of
round trips. If you have 20 transactions a minute, this is just fine.
If you have 2000 transactions per second, it totally sucks: you're
buried in round trips.

In my experience, if you want your application to scale to large
numbers of users, you need to avoid application<->database round
trips.

Best,

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca