RE: Stored procedures and J2EE

[The elephantwalker]

Rian,   If you access the stored procedures from a slsb, its just like you ran a jdbc from a stand alone program or a servlet, only you are using the middle-tier to provide the business method facade to the stored procedure. I would not recommend accessing the stored procedures through the cmp...use a slsb. If a create in the cmp is used, and the stored procedure is necessary for the create or postcreate, use the slsb from the cmp create or postcreate.   An example of stored procedures is Jive's forum application. It uses stored procedures directly from jdbc calls. Jives runs the java forums on the sun site. I believe the code is freely available. I think the message filters are stored procedures, which means that every access of a forum message goes through a store procedure from a servlet...and this forum is pretty darn fast....so there should be no bottle neck from using jdbc and store procedures.   As for migrating the business methods from the stored procedures to the slsb's, this can be done more easily if the slsb is used as a facade for the cmp. Thus you can use business methods which require direct jdbc access (count(), for example versus a cmp findAll().size()) or cmp access or stored procedures (again, jdbc access) as necessary for performance.   So...if the architecture is properly implemented, there should be no hit on performance.   Regards,   the elephantwalker   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rian Schmidt Sent: Thursday, September 06, 2001 9:51 AM To: Orion-Interest Subject: Re: Stored procedures and J2EE I'm interested as to how you can say this... we just did a series of tests here to see what the effect of pulling out some fairly complex stored procedures into CMP beans, and the performance impact was enormous.  We've actually gone the other way, that is, developing stored procedures for each anticipated database.  The fallback is that the logic is done in the beans, but that is a worst-case scenario.  Now, I realize that this would be considered such bad form in a Sun-controlled world of pure J2EE that I hesitate to even mention it... but in the real world, any significant hit on performance is enough to convince us to denormalize a bit, so to speak.   I don't think that you can say "there's absolutely no hit on performance" not to use stored procedures, particularly if that procedure requires repeated queries of the data in a pseudo-recursive way.  Do you really think that any performance hit that we've seen is a result of poor design?  I'm really interested in your reasoning.   Rian ----- Original Message ----- From: The elephantwalker To: Orion-Interest Sent: Thursday, September 06, 2001 2:23 AM Subject: RE: Stored procedures and J2EE   As for distributing your business logic between the datastore and middle tier...aren't you making your life more complex than it needs to be? There is absolutely no hit on performance if you pull out all of your business logic into a slsb or cmp...there's just no need to use store procedures any more.