Re: Writing most code in Stored Procedures

Guy Rouillier wrote:
> I have a thread I started ages ago over on the PERFORM list that I'm
> sadly just now being able to provide some insight on. I'll be replying
> on that thread in more detail, but the short of it turns out to be that
> at least in this one application, using stored procs for inserts is
> slowing down the app considerably. The app does high volume inserts and
> updates, about 16 million rows a day. By switching from stored procs to
> inline inserts, elapsed time dropped from 2595 seconds to 991 seconds
> for a test run.
>
> So the moral of the story is that, as anyone who has worked
> professionally for a number of years knows, no magic bullets exist.
> General guidelines can be helpful, but each scenario must be
> individually investigated.

Absolutely. You can't assume that every application is going to fit
neatly into the same development jig. Hope the MVC frameworks zealots
don't read that. <g>

I worked on a fairly high volume site using PG, an ad trafficking
workflow application, which imported ~2 million placements daily from
DoubleClick, OAS and Accipiter. Everything had to be imported and the
reports run and cached by 8am so the clients stare blankly at 10,000 row
Excel charts over their morning coffee.

Moving all the application-bound inserts into stored procedures didn't
achieve nearly the performance enhancement I'd assumed I'd get, which I
figured was due to the overhead of the procs themselves.