Hi Thomas, > > [JCT] I guess then that if the procedure is being used heavily and > multiple > > times there might be a real performance gain from using procedures > over > > straight SQL. > > > Most likely. There is really no need to use stored procedures, unless > administrative ease is a necessity. You will get the same advantage > (for > sure) if you use straight SQL in a PreparedStatement that you keep a > handle to in your jdbc client. > The purpose of PreparedStatements are just that - they are precompiled > (or prepared if you like) - and ready to use to harvest the performance > gain.
[JCT] Hmm, I have thought about this a bit more and I have come to the conclusion that there are NO advantages (at least for me) in using Java-based procedures and there are in fact reasons not to use them. I feel this way because, as you say, you can get the same perceived benefits from simply using a PreparedStatement but the main reason is the maintenance nightmare that they introduce. I have just cut 7 procedures and every time I move the class they are defined in or rename something or change a parameter I have to recreate the stub that exists in the database. It is incredibly easy for them to get out of synch. I am going to switch to just calling Java methods that don't have any representational baggage in the DB. Cheers, John
