Guy Rouillier wrote:
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.
I would suggest separating the strategy of server-side biz rules from
the implementation method of stored procedures. You can also implement
biz rules as triggers and stick with inline inserts, updates and
deletes, which is what we do in my shop.
That being said, the enforcement of the biz rules has to be done
somewhere, and no matter where it is done it will involve disk reads and
validations. It always *seemed* to me that putting the code on a table
trigger involves the least possible overhead for doing these things. It
provides complete real-time enforcement within the transaction with no
network overhead, and has the simplest possible interface: plain old SQL.
--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com www.andromeda-project.org
631-689-7200 Fax: 631-689-0527
cell: 631-379-0010
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
