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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
