Re: Why are stored procedures looked on so negatively?

On 24/07/13 02:56, Andrew Sullivan wrote:
> 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
>

Thanks for the response. Obviously since I am still in the development
stage I have no idea of the number of transactions I will need to handle
but the business has the potential to be quite popular so I'd rather be
safe than sorry and be able to handle large amounts of traffic from day one.

I think ultimately it'll be simpler this way because the system I am
developing is a quasi distributed system with lots of independent parts
that need to be able to communicate and to share data with each other.