Helpful ideas and solutions for the Oracle application enthusiast. Check out the archives

Wednesday, January 18, 2006

PL/SQL vs J2EE

Let's say you're designing an enterprise-wide OLTP Web Application with Data Warehousing, Reporting and multiple interfaces to various external systems for large volumes of data. Where do you put your business logic? Do you embed your SQL into your Java code, or do you keep your SQL in the database, and write stored procedures?

Regardless of your answer, you are wandering into one of the older and more heated arguments in this community. (Classic examples of one: here and here) I don't mean to re-ignite it, but I do want to understand it.

"It is a religious war though, they cannot be won by reason or objectivity." - Tom Kyte

It would appear like Java programmers hate stored procedures. They are viewed as hard to maintain, and not scalable. To them, scalability means spreading out the workload, running on several small machines.

Some don't even like to write the SQL themselves, instead preferring to use a framework like Hybernate or TopLink do it, and then rest their heads on their pillows at night dreaming of fewer errors/portability issues and better performance. But even if they can't achieve the better performance the database guys keep bragging about, they're more than happy to accept greater ease of development and maintainability in exchange.

Plus, Java programmers love choices. Lots of tools, unit testing packages, libraries - all open source - not to mention a huge community. They wouldn't want to get attached to any database vendor, especially one that is expensive and proprietary.

"If you want portability keep logic out of SQL." - Martin Fowler

At the other end of the spectrum are the database specialists. They walk around with print-outs of all the horrible SQL statements Java programmers have written that are choking the database, with serialization and loads of parsing. "Leave the SQL to us!" they plead in vain, "it's too hard for you, especially with this crappy database design!" They may secretly admire the Java progammers ability to write procedural or object-oriented code, but they believe that they lack the key to unlocking database performance: "set-based" mentality.

They look at a Java application and all they can see if multiple database trips where the SQL could have been combined into a single stored procedure making one trip. They examine their EJB containers and then lecture the programmers on recognising and understanding the difference between read-write and read-only situations.

The database specialist isn't as impressed by the Java programmer's triumphant removal from database dependence, for if you aren't tying yourself to a database you are losing out on all its power.

Where does that leave us?

The fundamental question is where the business logic should be.

The Java programmer, who sometimes refers to it as domain logic, wants business logic out of the database, often in the name of database independence. Even without this argument, they see live or non-relational data, in different formats, much of it transient and with complex rules ... why write piles of unmaintainable PL/SQL for that? They may concede that some validation constraints are OK, but even that isn't for sure.

"Do not use stored procedures to implement business logic. This should be done in Java business objects." - Rod Johnson

The database specialist views business rules as data rules and thus belongs close to the data. If there are some business rules or business relationships between various entities, well that should be enforced in the same place where the data is stored: the database. You've bought expensive, sophisticated software capable of managing your data in a fast, reliable, scaleable way: use it!

I don't think there really is a right answer and a wrong answer in general terms. But armed with an awareness of the debate, its basic framework and a few starting points, we can make the decisions that make sense on a project-by-project basis.

Note: This is one of the first times I've put up a non-technical post, so I'll be curious if there is any demand for more.

Having over 12 years of developing database applications, I learned long ago that the database is hands down the best place to store the business logic. It's been said before, but front end applications come and go, but the database (and your data) live on.

Most J2EE developers that advocate putting the business logic in the application are writing rinky-dink applications where it's possible to get away with being "database independent". Most aren't writting enterprise level applications where scalability is a real issue, so they haven't had to learn the mistake in their approach the hard way.

I believe too, that a lot of J2EE developers look at having the business logic in the application layer as a type of job security. By keeping the logic in the application, it is more difficult to switch to a new development platform when something better comes along.

Unfortunately, it is a religious war and no amount of discussion seems to change the mindset of the J2EE heretics.

Once you've got past the technical fallacies on scalability issues etc, the arguments for removing business logic from databases crystalize into a case for commercial expediency -- that it's easier to shift the software product if it works on multiple database platforms.

This, IMHO, is why so many commercial off-the-shelf (COTS) solutions perform so poorly. They are database independent and lowest-common-denominator methodologies that are not technically efficient predominate. Thus a lower development cost for the software developer translates into a higher hardware cost for the purchaser.

Again IMHO, if proper software design methodologies are used then the actual fingers-on-keyboard coding of the business logic (as distinct from the GUI) is a surprisingly low proportion of the development effort. So it ought to be commercially viable to support business logic embedded in Oracle, SQL Server, My SQL and DB2 without a prohibitively expensive premium, when life-cycle costs and performance are considered.

I was never smart enough to be a developer or even a real DBA ;-), so I ended up as a business systems implementor and MIS manager. Based on 12 years of that work, I can only beg, plead, and beg some more that architects and developers take the Tom Kyte approach: everything where it makes sense to do so goes in the database. And it makes sense for a very very large percentage of most business applications! I can't rehash Tom's arguments 1/10th as eloquently as he makes them and won't try, but I can report that out here at the sharp end the database is the most reliable and managable piece of the whole puzzle. Often it is the _only_ thing we can rely one.

Well, off to vist 97 workstations to update Microsoft Registry keys for one of our apps. Yeah, that is productive. Luckily that means I don't have time to give you my views on Java designers/programmers....

I wander how this simplest 3 table join was called "complex query". Dunno...wow! Give him a real complex query with analytics, query rewrite capabilities and let's see how this "generic java code approach" would (*NOT*) perform.

well I come to the blog rather late...but I thought I'd still say I liked the post.

What I'd love to do is mix java and pl/sql Now is there some secure way to pass asecure connection from java to pl/sql? This would solve so many problems for me, but making users log in twice isn't really an option for me

It is not only a technical, but even more so a cultural problem.In the J2EE (and increasingly .net) world, DBAs are seen as the ones who have cultivated the art of non-movement to perfection.Some may even break out in violent insults when they hear that there may be something that can be done outside the database. Sometimes they look misearable, and I assume that it is due to the fact that they have to expain the obvious truth again and again to young and stupid people.No wonder that application developers try to avoid contact!There are situations when I would love to call a stored procedure, but I know that when I ask my DBA to provide one, endless discussions will take place, including that the whole project is crappy because stored procedures should have been used all the time. Now I imagine that I want the DBA to change something in "his" procedure...I end this post with qoute from Douglas Adams:

Many were increasingly of the opinion that they'd all made a big mistake in coming down from the trees in the first place. And some said that even the trees had been a bad move, and that no one should ever have left the oceans.

As the writer of the article, towards the end, has said it correctly "There is no right or wrong answer". I think it really does not matter in which language you code the business logic as long as it is easy to understand, easy to maintain, works great and most importantly cost less to do anything with it. At an enterprise level, tell me why would some one change their database, it is a huge effort as well as costs too much. Also how much choice we have left with , just 3 - Oracle/SQL Server/DB2, that's all. So if some one codes it in database, fine, as long as there are enough people available in the market to maintain it.

Lastly,I feel there are better tools to debug/test/enhance Java code than PL/SQL code, I might be wrong, but certainly it looks that way.

There's definitely a question of general preference here. After all, you can definitely get a working application either way. I don't think it would be possible to prove that one approach is fundamentally better than the other in all cases. My personal preference tends to be in favour of making the application logic reasonably separate from the data representation. To me it's a question of separating concerns, not a religious question of app vs. dbms. If I were writing a program that didn't use a dbms, I would do the same. If I were writing code inside of a dmbs with pl/sql or t-sql or what have you, I would still try to do the same.

Guys, this is not a war as many DBA fans/gurus vs. java fans/developers think it is. I would like to use common sense when I develop a new app or migrate an already existing one.

From one end, for those who say one should embed business logic into the database (using stored procs), I'd say you're building a bulky monolithic application that tends to become hard/expensive to maintain from day 1. Change management will be costly in the long run as well unless you work with a team of pl/sql developers. As Prasanna mentioned earlier, we need this separation of concern between the data persistence layer and the application logic layer to clearly distinguish between raw data itself and it's lifecycle through CRUD operations which don’t really mean anything outside of the application logic itself. That's exactly what Martin Fowler and Gavin King were trying to mention in their referred sentences from the original post of this blog.

On the other hand I believe in using the tools with their best use. DBMSs are best used for storing/maintaining data weather through sql or stored procedures while application servers are best used for handling client requests and sessions in addition of managing/offering many other services like transaction management, session tracking, service portability, horizontal/vertical scalability, security management and many more offerings depending on the brand/open sourceness and the size of the community that supports them. The final layer in an enterprise application stack is the UI. There are UI frameworks that are best used for handling only that, so one can use the best framework for her/his needs.

So if I’m given the option to choose between creating a new application with the business logic embedded in the DB and using three separate layers for this same application, I’ll choose the latter for the ease of use and the flexibility offered in each layer. That doesn’t exclude the fact that I’m against stored procs, in the contrary, I believe PL/SQLs are best fit for cleaning up/maintaining new and old data in an already running system.

"My personal preference tends to be in favour of making the application logic reasonably separate from the data representation. To me it's a question of separating concerns, not a religious question of app vs. dbms. If I were writing a program that didn't use a dbms, I would do the same. If I were writing code inside of a dmbs with pl/sql or t-sql or what have you, I would still try to do the same."

Think of a DBMS as merely data storage for a moment, and then compare it with XML, memory or file-based storage mechanisms. You would not put business logic in any of the latter, so it could be argued that one should not put business logic in the DBMS!

As has been said, upon comparing PL/SQL and J2EE, one does not appear to offer benefits which outweigh those of the other. My personal preference is business logic in J2EE. My reasons are simply:

- J2EE is Object Oriented- More libraries (and reusable) code available for J2EE- Web Service supported in J2EE and can be more easily built calling existing business logic within J2EE objects.- IDE's, tools, etc are more advanced and widely available for J2EE.- J2EE is open source (pretty much free apart from support contracts, etc) whereas PL/SQL is proprietory - i.e. You are forced to go with Oracle (and pay large amounts of money).- Larger community of developers for J2EE

My large experience in T-SQL or PLSQL is clear, these languages are from the past, only used for big batch processes not to implement bussiness rules of any system.

This languages are not OO, programming environments are poor, is not a scalar language, the process to hot upgrade that systems with embedded languages are horrible! and you are linked to a propietary database for all your life.

It's a suicide to implement bussines logic with any embedded languages.

As the last two posters have already pointed out, this is not a question of PL/SQL vs J2EE but actually a traditional non OO design vs a OO design.

Do keep in mind. Just because one is using Java or J2EE doesn't really mean one is implementing a good OO design.

What is good and what is bad? I would steer away from the technical pov of OO for a moment. In a corporate environment, should you play team and walk the extra miles just to make your colleagues consume your works easier and hence let them look good and you look bad? Or be selfish.

It's an old debate, and in my experience almost everyone is better versed in the J2EE side of the debate. The side in which most people need more education is on the RDBMS side.

For instance, I admit being tied to a database isn't ideal in some circumstances, but we've heard that over and over. Does anyone talk about how it is just as impractical to be tied to an application?

I've supported a variety of systems for about 15 years and I'm telling you that people use the back doors ALL THE TIME. They won't always use your application to access or modify the data - sometimes they write their own apps or even use straight up SQL.

If there's nothing in the database to protect the logic of the data, sometimes your application won't even launch. Now who's committing suicide?

Object-Oriented programming may be great in many cases, but some people walk around with it like a hammer thinking everything is a nail.

If the fundamental purpose of your application is the storage, manipulation and retrieval of data, then why is OO better than a set-based transactional model? In this particular case, performance and the validity of the data trumps the speed and comfort of development, in my view.

In any event, as professionals we should learn how to use more than one tool. That's all I'm really saying.

Yes in an extend, I agree, it may take more "education" on the RDBMS side. But it will take more than just education or merely learning "how to do it" to become a good OO designer. Think about the difference between a musical instrument player and a composer.

You can consider J2EE in general as a tool or a set of tools, in my experience an overly hyped clumsy tool. OO itself is a different philosophy. Transitional procedural language is about solving a particular problem. Whereas OO is about solving the problem once and forever. It is just a totally different way of thinking in programming or problem solving.

For end users or non OO programmers, an application may be just an application. For a good OO designer, an application is nothing more than just the presentation layer. The core is the framework with layers.

I've over 20 years experience with Oracle and from about the mid 90's started coming across this idea of making "the app" independant of the database. Designers (now called "architects" for some bizarre reason, I wonder what Sir Norman Foster would make of that) love setting their stall out by sayinbg they want to be database indenpent so that if they ever decide to change vendor it will be easy.

In my 20 years the number of times I have come across a enterprise system being moved from one vendor to another is precisely 0. It is actually FRONT END application deficiencies or flavours of the month that have dictated when a "refresh" is required. I have seen the fronts ends re-written , migrations from one database to another to fit a new application or database upgrade, but never a purely back end swap from one vendor to another as envisaged by designers at the outrset.

The approach of database independance or portability is a valid one for companies that produce software for sale. Howver, far too many people who simply work in IT departments of big companies are thinking they work for a software house and could face this scenario, when in reality they will hardly ever be faced with anything of the sort.

An example I can give of a enterprise class system that has a different code base for each target database is TIBCO. I've worked with the Oracle version and not only did I see stored procedures but also AQ. I assume they must use similar offerrings in each of their database flavours.

Let's not also forget what tends to happen on applications which are well advanced in the testing phase of the project and an issue is found that threatens to derail the timescales. You will all no doubt acknowledge the collective sigh of relief when it is discovered that the issue can be fixed "in the database" via a patch rather than having to re-release/re-deploy the application. Then starts the slow march of the logic into the back-end; nothing the threat of several late nights fixing and re-releasing the app to start people thinking that actually it's OK to have business logic in the database. This in itself says a lot about what is maintainable.

That said, it is a nice living one can make from tuning database applications written by clever people who have failed to build in certain optimisations in their "design". Long may it continue.

It's 7 years after the original article and it's interesting to see that the argument has turned into a win for Java... but at what cost.

I've seen companies go under while attempting the migration. 40 java developers to do the work of 5 PL/SQL developers. The inability to meet application performance targets set by the original PL/SQL application code. Massive blowouts in time and cost.

Despite moving business logic to Java has the database back end actually changed? Never. So what was the benefit to the company that did it apart from having to increase their staff 6 fold and suffer customer complaints due to performance degradation, massive new hardware requirements, significantly increased deployment complexity and significantly decreased stability???

On the plus side Java developers now have great job security (while the company remains solvent) because they are forever "needing" to port to the latest trendy toolkit every 6 months as well as rewrite large parts of the application to handle the changes in the latest java SDK version not to mention the server hardware and OS incompatibilities that creep in.

As for genuine defects in the business logic... Well it's too hard to fix them because all of the interfaces and Object Oriented Classes are too heavily used, the dependency networks are to complex to be able to modify them to fix the defect. It would mean a 12 month cycle to modify the design. We'll just have leave the defect there to design our future work around it. The defect becomes a feature meanwhile the PL/SQL developers are complaining that the fix should only take a day or 2 so where is the hassle???

Migrating to Java is the dotcom boom of the 21st century. You're not a trendy business if you're not doing it and many companies are going under because they spent too heavily in it.