I have stateless EJB's call with JDBC to Oracle Store procedures.
I am in dilema.. where to put more logic.. like validations.
In store procedure or Java calls.
Did somebody made performance comparison?
What are advantages and disadvantages of each one.
I will appreciate answers
I mean.. in Storeprocedures or in java code
We are also having same problems. Our Architecture uses Stateless Session Beans, that calls a stored Procedure to update records to the DB. We feel that, in general the performance of stored procedures are not that "Rosy", but the reason for opting stored procedure approach is to accomodate easy maintenance. By maintenance I mean If there are changes in the DB structure, we dont have to change the Enterprise Bean code and redeploy it.
I would also like to know the specific reasons (disadvantages) of stored Procedures.
Please do share with me why you opted the Stored Procedure approach. Awaiting reply.
In our project, we have moved logic to stored procedure
when the logic involves too many network round trips between
the app server and database. As far as code maintenance when
there is a schema change, you have to deal with it any way, srored procedure or java.
In a article by Scott Ambler, there is a little discussion about stored procedures. The bottom line for Ambler is that stored procedures are little better than a quick hack used to solve your short-term problems. I don´t know if a totally agree, but he has some strong points
Scott Ambler is an OO purist, and I already made a link to his viewsin this discussion
His paper are generally very consistent, high quality, although he think that everything should be OO, and this shows quite clearly.
Though he has a good point that if you think like him, evryhting should fit an Object Model and then stored procedures are a bad idea.
My point is that plain old structured programming combined with a good relational model are not necessarily bad, and in various circumstances can do the job better than OO.
You have to pick the right tool for the job (like you don't use object models for OLAP apps,also you don't use object models for bulk processing).
However, Bob presented here the case where he uses stateless Session Beans to invoke Oracle Stored Procedures, so Scott's arguments don't apply to his case, unless someone finds a good justification for him to reengineer the whole thing and make it OO.
The main reason to use a stored procedure over a Java program is usually for performance reasons. These days, there is rarely any other reason.
I'll use them if there is a performance problem in a database function (insert/update/delete/select). Usually in cases where there is lots of looping going on. Doing it in Java could potentially means hundreds (or thousands) of trips over the network between the Database and Application layer.
Doing in in a stored procedure means one trip. And then doing all of the looping inside of the database. HIGHLY preferred.
The big disadvantage I find to using stored procedures is version control. It is very easy to do version control with Java programs using programs like CVS/Perforce/PVCS etc. But checking in and out of stored procedures is a tedious process. And keeping stored procedures in sync between Development/QA/Production environments can also be a pain in the ass.
My basic rule is use them when performance dictates. Otherwise, don't use them.
Thanks for answer.
I see a big disadvantage for store procedure as it is not object oriented peace of code.
So it is difficult to use it as part of model of system.
If somebody had experiense with that please share.
And if storeprocedures part is big...as in my case.. then it became hard to maintanence and reuse.
Here's my take on the subject
1. Structured Programming is not necessarily bad.
So I find it enjoyable programming PL/SQL as well as programming Java. Even PL/SQL has some specific notion of Object types - of course not as powerful as a full blown OO language but the basics are there. I even don't take advantage of that because I'm comfortable enough with structured programming.
2. If you do want to stick to an OO metaphor, and still you have to use Stored Procedures, you have to option when using Oracle 8i:
a. write Stored Procedures in Java (not sure if you're DBA will agree to run the Aurora VM inside the database, and also it's not advisable for "heavy" tasks).
b. write Stored Procedures in C++ (there will have to be a "glue" function ), and embed the code into a DLL or Shared Object for Unix.
This second approach is definitely more OO, but is relatively new, so it must be used with caution and with careful testing.
One big thing that you have to pay attention to , is that stored procedures may introduce subtle inconsistencies as they bypass your app server view about the data, some Entity EJB may become "dirty" without the app server having a chance to notice that.
a. Either don't use Entity Beans at all , and use stored procedures from you Session Beans. (Entity Beans are a bad idea anyway, but that's just my opinion so disregard it if you think otherwise)
b. Or use Entity Beans, and make sure that Entity Beans don't overlap with any data updated by the stored procedures executing in the same transaction (tough, isn't it ?). Even if the two sets of data may intersect make sure you understand and manage the implications (even tougher)
c. Don't use Stored Procedures at all, and you're free to enjoy the OO benefits of Entity Beans (whatever they may be) without worry .
3. As a performance comparison, I think nobody has figures.
It very much depends on the kind of logic and processing you are talking about so it can be the same for some things while it can be 10 times slower for other things.
But don't even try to think that you'll get even 25% of performance if you put in EJBs logic that normally belongs to stored procedures.
4. As for mainteinance thing, stored procedures can be put in CVS as well, after all PL/SQL is still a text based language.
Last thing I wanted to tell you, since you seem to be so fond of OO, is that many hard core OO authors think of Stored Procedures as a bad idea.
You can see such an argument made by Scott Ambler at www.ambysoft.com.
So, after you weigh all the arguments you can decide for yourself, and let us know your view.
Thanx for the valuable comments.
As I said before, our Project demands, User customizations. The user customization is done by the implementation Team or the end user. We DO NOT want the implementation Team to meddle with the Java Code and the EJB code.Even if we did allow, the implementation team has the overhead of compiling and "REDEPLOYING" the EJB(Which we guess is cumbersome).
The team could manage making changes to the stored procedures n that is the only reason for having the "Custmizable Part" in the SP.
Thanx once again for your valuable comments,et about the OO affinity, we would rather prefer having SQL in the EJB layer and manipulate data in the middle layer.
I guess we should not be having problems with Version Control of PL/SQL.
I have done the customization thing that you are thinking about. We even took it a step further allowing an administrator to create innumerable stored procedures, provided they had a preset signature, returned comparable ResultSets and their names were listed in a configuration file.
You need to have version control absolutely nailed before you try this. I was using SQL Server which made it worse than PL/SQL but it is not something to take lightly. This becomes a deployment issue as well; your "implementors" will need to have a good handle on CM and a firm understanding of the pre and post conditions of your SPs.
That said, it worked for me and should work for you.
Ok Rick ! I feel u have a big problem of maintaining version of stored procedures. You can use Oracle Designer/2000 which will allow you to keep version of db design as well as the code of db objects. And above all if u are using cvs u can put ur stored procedures in CVS also.
Using stored porcedures is the most convenient thing to interact with the db as it takes very less time to make changes on account of db structure changes.
So go ahead and continue using stored procedures !
Generally, I only use stored procedures when performance problems dictate.
IMHO, stored procedures do not encourge reuse of existing code, and they are not object-oriented at all. Worst of all, usage of stored procedures spreads application logic across two tiers -- which I think is a common design flaw these days. Having application logic across multiple tiers creates version control, configuration management and deployment problems. Especially if you want to be able to have different versions of a stored procedure running against the same datastore -- which is common on large projects.
I am a person who feels that deployment processes, version control, and SCM should be almost 100% automated. It has been my experience that extensively using stored procedures creates a lot of manual work using several different tools to keep them in sync with other application source code.
But again, that's only my experience.
I totally agree with you, on the "Having Business Logic in 2 tiers" argument. YES, it is a potential Design flaw. But when there are lots of customizations and Table changes involved, we dont have any other option, do we ?
The implementation & Maintenance are of concern and YES Configuration Management is also a potential Issue, but as Dilip pointed out, we are using Oracle Designer 2000 so we dont forsee any issues in CM.
Thanx for your valuable comments,
I have used stored procedures in combination with oo-design in several projects and I want to make some comments:
1 Stored procedures can be used as an abstraction layer. It can be seen as a "lower" part of your domain model. It is important that the object model, the classes, drives the stored procedures (and not the data model). Logically you can think of a stored procedure as a private "persistence" method in the class it will be supporting. You group the procedures by naming them with the class name.
2 If you don't use stored procedures then the alternative will be to have SQL-code mixed in with the java-code. Java programmers do not always have enough knowledge about SQL (some has though) and this is often the reason for very bad performing systems. Project lead may have underestimated the need for skilled SQL-programmers. If you mix the SQL and java-code you will have difficulties when you want to review and/or optimize the SQL-code. Bad SQL-statements can kill i database or at least make it a zombie for a while. So place it (the SQL-code) in the the database so you can let SQL-expertise (they don't have to know java) review and optimize it. Large organizations usually has people with very good knowledge about SQL and database performance tuning. The worst case is when the SQL-code is automagically generated by a pertistence framework and "never touched by human hands". You will end up with a lot of problems but there will be nothing to look at (and fix). The SQL-code will be hidden in the "magic" code (often secured from you by copyright laws)
3 Large organizations usually has people with very good knowledge about SQL and database performance tuning. They have a steep learning curve (and to bad attitude to OO) to be used as Java-programmers in short terms but can play an important role if used as DB-programmers.
4 If security is a matter then you can deny "dynamic" SQL that comes "flying" over the network. The only SQL-code you allow is the one in the stored procedures wich are reviewed and supervised by skilled database programmers.
5 Your stored procedures will hide the database schema and will make it possible to make some changes without a single change in the javacode. And you dont event have to reboot your app-server. The datamodel (the tables) can be optimized, normalized for performance and your object model (the classes) can be a truly OO domain model. The stored procedures takes care of the model mismatch. The OO-purists do not have to se the datamodel and the DB-purists do not have to se the object model. (The best situation is though when everyone understands each other and work to serv one another). You always have to staff your project with expertise in each layer. You can not fill a project with "generalists"
6 When you make a strict OO-design and focus on the object model the early stages in your project always show bad performance. The most likely mistake you make is to answer business queries by fetching a lot of objects, examine them and then pick the right ones to answer the queston. In the long run you will always end up with optimizing with stored procedures.
7 You must have disciplined developers that always loads stored procedures from files that are version controlled in the same way as all the java source code and the DB-schema.
Procedures may not be portable between different databases. I you change from Oracle to SQL-server you have to port all your database code. It is not hard for a DB-programmer that knows about both SQL-dialects
I don't usually get into disagreements, but I disagree completely with your comments. I'll address a few of your points.
1) Stored procedures provide no kind of abstraction. Abstraction is an OO concept that stored procedures cannot abide. They cannot extend each other. They cannot force child classes to override methods. They are not polymorphic. They don't have any of the redeeming qualities that truly make abstraction or abstract design possible.
2) Putting SQL in Java code is not new. And putting SQL in C++, C, COBOL or any other language is not new, either. And 99% of SQL is easily optimized with testing and refinement. Most SQL, you can optimize with tools like SQL*Plus or TOAD. Once you are finished, you can copy the SQL into your Java code. Quite simple.
4) What do you mean by denying dynamic SQL that comes 'flying' over the network? If someone has access to run a stored procedure, then they have access to do equal amounts of damage. The stored procedures would have the same capability as SQL in the Java code (insert/update/delete/select). What difference does it make? Unless you are worried about someone executing straight DDL. And that can easily be protected from within the database without using stored procedures. I know this can be done for Oracle, MSSQL, and MySQL.
5) No kind of real schema change could be made without changing the application code. The only thing you could do is change column names. But if you mess with anything else, you'd have to change the corresponding Java object. That includes data types, precision and column width. Most likely, code would change in the business logic and/or in the presentation layer
The object model DIRECTLY affects the DB schema. DBAs are not application designers or object modelers. IMHO, DBAs should stick to tweaking the database and general DB administrative tasks. I see way too many DBAs who think they understand how to design the backend schemas of applications and often fail. Good OO modelers know how to take an object model and turn it into a normalized DB schema. But the schema design should be derived from the object model. Not the other way around. Strict OO design does not mean bad performance. There is no relationship there. Bad designs in any pattern (structured or OO) will yield bad performance. Good designs will yield good performance.
Thank You very much for your comments. I hope You let me answer because have to.
1 Abstraction is not an OO-only concept. Something can be an abstraction even if you can not apply all core OO features on it. The word and meaning existed long before anyone knew about OO-technology and has been used before in computer science to (Data abstraction and/or functional abstraction). But I understand if You have another definition and I respect that for sure. But what I meant was that the SP can hide the database schema and be a more simple interface to the actual SQL-code.
2 I dont understand Your point. Of course there always has been a lot of SQL-code mixed with ordinary source code. I aggree. But my point is that to much bad sql-code has been there and its not because of the lack of tools. Its because the mix of two completely different cultures. Sometimes you find people that can handle this but there is a propability that a skilled Java-programmer is a not so skilled SQL-programmer and vice versa. When data change (grow) the SQL-code that worked fine may not be that good anymore and you have to fix it. Do you search for the SQL-code in your java source code, copy some large SQL-statements to SQL-plus, change it until it works better and then paste it back again?
4 Stored procedures can be reviewed and tested in advance. Dynamic SQL can not.
5 With stored procedures its possible to change how enheritance and relations are implemented ( ie in favour for performance or normalisation) without changing a single line of java code.
My point is that its good if you do not mix the data-model (storage model) with your object model and the use of stored procedure can help and be a fairly stable layer between the two models. They can also be very good if you want to hide a legacy database you can not get rid of because there are legacy systems that depend on them.
OO-purists (and I am one of them even if You dont believe it) often think that relational databases are bad and should be replaced by an OODBMS. This can lead to the mistake we do when we think that we don't need to divide our system into several layers. Each layer responsible for at certain type of tasks.
Make wonderful systems however you do it.
Well, I'll wade into this one. "Completely disagree" was a little too strong a statment given the comments you provided.
1) Abstraction. Stored procedures cannot inherit but they can delegate. Also stored procedures can implement a common interface (Though verification will be required at runtime).
You can have 1..n stored procedures that all take the same parameters. The application picks one and executes it, no matter which one is chosen a generic ResultSet is returned. It's not unlike the Strategy pattern.
2) SQL in java code. I write tons of outrageously complicated dynamic SQL and I have to agree that it often needs to be in the java code. However it is often possible to put SELECT lists in constants, configuration files or static classes without harming anything. It amazes me the lengths programmers go to avoid duplicating a line of Java, yet the abandon with which they copy and paste SQL. Separating SQL Strings into easily identifiable regions is a very noble goal.
Opening another can of worms in this forum, views occasionally provide superior FROM clauses than SQL.
4) Besides testing, there are security benefits to using stored procedures, but it takes a deeper understanding of databases to see it.
From SQLServer 2k Help:
For example, in an archiving scenario, stored procedures
can copy data older than a specified interval into an
archive table and then delete it from the primary table.
Permissions can be used to prevent users from deleting the rows from the primary table directly or from inserting
rows into the archive table without deleting them from the
primary table. You can create a procedure to ensure
that both of these activities are performed together, and
then grant users permissions to execute the procedure.
5) You can do complete OO design without creating any "entity" classes. I have used a configuration repository to dynamically build object graphs that build sql and retrieve beans from the database without the name of a single column entering the code base.
Generally if you give a DBA good documentation, they won't break things.
It is foolhardy to say that the model should always drive the data (or vice-versa). They should collaborate. What if the database already exists? There are lots of times where the reason to go to OO is that it can talk to your legacy database without being bound to it.
Thx for your comments. I'll go ahead and respond. First, "completely disagree" may have been strong, but I only meant to say that I disagreed with the spirit of the message. I meant not to offend, and apologize if I did.
1) You are reinforcing what I said earlier. You cannot get pure abstraction from SPs. Not in the way that you can from an OO language. Which was exactly my point.
2) I agree. Developers who tout reuse and then cut and paste SQL for the same tables really aren't "practicing what they preach". This is a horrendous practice. Because if the schema changes, you end up changing code in several places, potentially. Not a good thing.
4) You're talking about an exception case. But exceptions should never dictate the design of a system. That is by definition an anti-pattern. Of course, I acknowledge that there are certain scenarios where SPs are warranted. I'm simply saying it is better to use them only when necessary as opposed to using them exclusively.
5) I agree. And EJB containers do this. CMP is widely used and a column name never enters the code. I personally don't subscribe to it, though. I have always felt that BMP is much safer, portable, and customizable than CMP of any kind.
It isn't foolhardy to say that the object model should drive the data model. I think 'foolhardy' is a little strong of a word, there. If the database already exists, then OO concepts can still be adhered to. But undoubtedly there will be code at the persistence layer to take the non-normal data and put it into OO-style data objects. This is common -- especially for real-time legacy integration. But it isn't ideal. If real-time wasn't necessary, I'd tend much more towards exporting the data into another database containing normalized tables driven by the object model and then using THAT as the data store.
It really depends on the goal of the project. If the goal is to just to write an interface to do some queries to a legacy datastore, then fine. But if it is meant to be a REPLACEMENT, then the datastore design should be rethought as well, IMO.
But we can agree to disagree.
You can have validation in three places:
The lower you place the validation code the more secure you will be that your data is consistent. So you will always have validation in the database or as low as possible in he middle tier. Stored procedures can be the "secure gateway" that ensures data consistency.
BUT you dont want to make "round trips" from web-layer to database just to validate data so you can mirror rules in the web-tier with java-script. But you can not rely on a system that does all validation in the upper web-tier.
Performance for what?
If you mean performance for fetching objects you will get a performance gain between 2 to 100 times better performance. It depends on what you compare with.
2 to 10 times if you compare "dynamic SQL" interpreted at run time with precompiled preoptimized stored procedures.
5 to 100 if you compare CMP entity beans with stored procs.