On Mon, May 08, 2006 at 04:17:53PM -0700, Casey Duncan wrote: > From personal experience (and others will disagree), I find putting > logic in the database to be a bad idea. I only use stored procedures > for triggers and the like, and I try to avoid those whenever possible. > > Here are my reasons why: > > - I don't like the PL/pgSQL language (yes there are alternatives, but > they have their own drawbacks) Huh? Just use whatever language you're already writing in. > - It's complex to test and upgrade (we actually wrote non-trivial > infrastructure to automate both) Uhm... CREATE OR REPLACE FUNCTION. Unless you're changing parameters it works fine. As for testing, I fail to see how it's more difficult than testing the same thing using external code. I will grant that unit testing is harder though, since you don't have as many opportunities to hook into the code, but if you create a set of known test data it's not all that difficult. > - It's difficult to debug (compared to external languages like python > or java) > - It's difficult to profile, therefore getting good performance can > be difficult Actually, I think there's a commercial product that allows you to do both, but I'm not sure. It would certanly be nice if it was built in. > I had a very complex system coded in stored procedures that performed > poorly and was hard to maintain. It's now refactored into java/ > hibernate code that's simpler to understand, performs much better and > is easy to extend and maintain. Of course that's just my particular > case and obviously YMMV. If hibernate is performing better it's due to application design. A lot of times people try and approach database development the same way you'd approach procedural coding, which is a bad idea. Hibernate and other products go to great lengths (ie: caching) to try and make procedural coding techniques work well on databases. > Stored procs could make a lot of sense if you have many different > clients accessing the db in different ways and you want to strictly > enforce business rules across all of them. I had no such requirements > in my case. You sure there won't every be any other apps hitting that database? :) Part of how Pervasive makes money is dealing with exactly that kind of attitude... "nothing else will ever have to communicate with this system". > In any case I would strongly recommend doing the simplest thing that > you can get away with. If your business rules can be fulfilled with > grants, views and constraints alone, use them. Or maybe more accurately, do what you have the expertise for. If you've got a good database developer on staff there's a lot to be said for putting stuff into procedures, especially if it's database-intensive. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
