From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | PgSQL General ML <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Writing most code in Stored Procedures |
Date: | 2007-08-17 23:43:49 |
Message-ID: | 46C632B5.50703@cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Manes | 2007-08-18 02:45:49 | Re: Writing most code in Stored Procedures |
Previous Message | Merlin Moncure | 2007-08-17 23:41:11 | Re: Blobs in Postgresql |