Search Postgresql Archives

Re: Writing most code in Stored Procedures

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



-----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-----

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux