Re: Writing most code in Stored Procedures

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/17/07 18:00, Steve Manes wrote:
> 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.

Would that be because the original app was written in a compiled
language, but the SPs in an interpreted language?

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGxjK1S9HxQb37XmcRAnYWAKCRV+INrpvl83lhnt4iadIMrBNIRgCgr8J2
UK3F87ji/24mrISLl+WmLnY=
=5csM
-----END PGP SIGNATURE-----