Обсуждение: little off-topic: stored procedures
I'm right now choosing the design patterns for an web portal for an academic institution. I just started a philosofical discussion about how we will do database access. I was thinking about use stored procedures extensively, but some people says me I should avoid them whenever possible, and put the business logic in Java. What you all think about it? Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Faculdade Ritter dos Reis www.ritterdosreis.br felipes@ritterdosreis.br Fone/Fax.: (51)32303328
> I'm right now choosing the design patterns for an web portal for an academic > institution. I just started a philosofical discussion about how we will do > database access. I was thinking about use stored procedures extensively, but > some people says me I should avoid them whenever possible, and put the > business logic in Java. What you all think about it? There's never a right or wrong way. In general, I've stayed away from stored procedures because they aren't portable and a few databases don't even support them. Clearly, there are advantages in having a lot of database work taken care of within the database, saving the interaction overhead between the client and server. For example, we have quite a few objects that require mutiple calls to fully delete them, and a stored procedure would work well. This is also a better strategy is there's multiple ways the database can be transformed, and not just from a database app that you've written (i.e. another app, possibly written in a different language, via the SQL command line, or using one of the various SQL-GUI tools). An advantage to keeping in the Java code is it's more portable and of course the "stored procedure" is written in Java, so learning db-specifics isn't required. Also, there's a clarity advantage when database interactions are all in one place, making it obvious how the data is created, queried, modified and deleted. Good luck... David
Felipe - I like having sql outside of the java code but i'm not crazy about stored procedures - you can check out an alternative approach to stored procedures and embedded sql at http://www.ibatis.com/jpetstore/jpetstore.html On Sun September 8 2002 11:35, Felipe Schnack graced us with the following - > I'm right now choosing the design patterns for an web portal for an > academic institution. I just started a philosofical discussion about how we > will do database access. I was thinking about use stored procedures > extensively, but some people says me I should avoid them whenever possible, > and put the business logic in Java. What you all think about it? > > Felipe Schnack > Analista de Sistemas > felipes@ritterdosreis.br > Cel.: (51)91287530 > Linux Counter #281893 > > Faculdade Ritter dos Reis > www.ritterdosreis.br > felipes@ritterdosreis.br > Fone/Fax.: (51)32303328 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Tue, 10 Sep 2002, Clinton Adams wrote: > I like having sql outside of the java code but i'm not crazy about stored > procedures - you can check out an alternative approach to stored procedures > and embedded sql at http://www.ibatis.com/jpetstore/jpetstore.html > > On Sun September 8 2002 11:35, Felipe Schnack graced us with the following - > > I'm right now choosing the design patterns for an web portal for an > > academic institution. I just started a philosofical discussion about how we > > will do database access. I was thinking about use stored procedures > > extensively, but some people says me I should avoid them whenever possible, > > and put the business logic in Java. What you all think about it? Personally, I like having it right in the Java, for several reasons. 1. You don't have to go look into another file to see what's going on. 2. When you build your code, you know exactly what SQL will be running. Otherwise you get whatever is in the database at that particular time, which might be different from what you think it is. 3. Rolling it into production is generally easier, because you can just roll out the Java stuff, without worrying about rolling out new code into the database at the same time. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
Hi! One reason to use stored procedures: Reduces the number of the required interactions between the DB and the client. It may result better response time for theclients, and still lower load on the server. Did you ever try to port your more difficult system to MySQL? :) I did, postgres did with 1 sql statement, what MySQL did with 7-8. The throughput was similar. It was the SQL problem, each vendor implemented differently, so stored procedures are the less portable things I have everseen. If the java stored procedures will be implemented, we will be able to emulate the stored procedure systems of comercialdatabases easyly. Laszlo Hornyak On Wed, Sep 11, 2002 at 02:02:25PM +0900, Curt Sampson wrote: > On Tue, 10 Sep 2002, Clinton Adams wrote: > > > I like having sql outside of the java code but i'm not crazy about stored > > procedures - you can check out an alternative approach to stored procedures > > and embedded sql at http://www.ibatis.com/jpetstore/jpetstore.html > > > > On Sun September 8 2002 11:35, Felipe Schnack graced us with the following - > > > I'm right now choosing the design patterns for an web portal for an > > > academic institution. I just started a philosofical discussion about how we > > > will do database access. I was thinking about use stored procedures > > > extensively, but some people says me I should avoid them whenever possible, > > > and put the business logic in Java. What you all think about it? > > Personally, I like having it right in the Java, for several reasons. > > 1. You don't have to go look into another file to see what's going on. Is this the thing called "spagetti-code"? > > 2. When you build your code, you know exactly what SQL will be running. > Otherwise you get whatever is in the database at that particular time, > which might be different from what you think it is. > > 3. Rolling it into production is generally easier, because you can > just roll out the Java stuff, without worrying about rolling out > new code into the database at the same time. > > cjs > -- > Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org > Don't you know, in this new Dark Age, we're all light. --XTC > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
A late follow up to this. Accepting the point about portability one of the great things about PostgreSQL is that it is free _and_ scalable. I think I'd happily use it on just about any project. Given that using stored procs is great. However, don't fall into the trap of creating rules about what should go into a stored proc and what should go into java. Some people suggest that business logic should stay in java and some people suggest stored procs are a good place for it. Put those 2 sorts of people in a room and you'll have an entertaining half hour. I prefer to follow the needs of performance. Things that will perform better on the server side should go into stored procs. Things that do a lot of memory allocation, or SAX processing, need to be represented as objects in java. It soons becomes apparent when you're coding if you've made a mistake. For example, whilst writing a stored proc the other day, I found myself wishing that PL had a Hashtable data structure. I sat back for a moment and thought about what I was doing and realised I was approaching the problem from the wrong end. I re-wrote the code in java. Even including making the mistake the code only took about 30 minutes to write and debug. One tip: encapsulate all your stored procs in static methods (maybe in a single class if you have few procs). Doing that means you _can_ rewrite them to java quickly (or hack the internals) if you have to. btw I don't agree about the idea of using Java as a stored proc language. I've investigated this and it's a royal pain in the neck. Java is not wordy enough to deal with *a lot* of SQL effectively. I am investigating alternatives to PL though, Lisp seems to me particularly interesting because of what might be possible with the idea of resultsets as closures. Nic Ferrier