Steve Manes wrote:
I'm fairly hardcore about keeping as much business logic as I can in the database. In fact, I only do SELECTs from the application, and usually via Views. All inserts, updates and deletes are via procs.
...
And, yes, it's faster. Particularly if business logic decisions have to be made in context with a transaction.
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.
-- Guy Rouillier ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
