Re: Myth of the database independent applications (Was: Are you using PL/SQL)

On Thu, 24 May 2007 15:43:17 -0700, zigzagdna wrote:

> Most of the software projects I worked on there are three layers (some
> people call them tiers, tier does not mean separate servers): User
> Interface, Business/Application Logic and Database. User Interface
> simply does user interface, displays screens, gets data from the user.
> Database stores data, there may be some triggers, but database does not
> use any stored procedures.

So why use them at all?

> Business/ Application logic is where all the
> logic/algorthsm etc are implemented.

That is the problem. This problematic design structure because different application modules can, and frequently do, apply different business rules to the same data. I had a case where a company had a table with two keys which were mostly the same but in approximately 20% the keys were different. The problem was resolved by adding a mapping table. How did this table came to have two keys? There were two applications accessing it and each generated the key in a different way. The table still exists, and there is a mapping table to help with the translation. The only way to guarantee that business rules are consistently applied to business data is to put them in the database, together with the data.

> Business logic calls database to
> get data and update/ insert/delete data. Thus database code is little.
> Using OO techniques, it is not all that difficult to write database
> classes in such as fashion that they are mostly database independent.

Of course, performance problems which will show up on every underlying database will be database specific. The application is database independent, the performance problems it causes are different for each underlying database. If your app works with a database in which readers block writers, like MySQL, your classes should look differently then if that wasn't the case. Establishing a database connection is expensive with Oracle and the application developers would do well to take that into account, while it's a very inexpensive operation with SQL Server and you can easily open one connection per window on your screen, without any ill effects.

> Sure there will be some differences from one database to another, but
> differences are lot less than if one was writing stored procedures in
> database proprietary language.

Yes, but if the application is written for Oracle specifically, and if it uses PL/SQL, it will easily outperform any database independent application. Also, dealing with the data inconsistencies, if the customer decides to buy another application, from another vendor, accessing the same data, will be a major problem.

> Business logic can run on same server
> as database server then network time is reduced drastically.

Unfortunately, this makes things much harder to diagnose and configure. One also introduces a possibility of interaction and mutual starvation of resources. If WebLogic is running on the same server as the database, its caching will significantly impact the amount of consumed memory, drastically decreasing memory available for PGA. Also, WL is quite expensive when it comes to CPU, which might adversely impact Oracle, also known for lavish CPU consumption.

> Middleware/
> interprocess communication provides efficient ways to communicate from
> user interface to business logic and to distribure business logic into
> many processes/threads. Business logic can use JDBC to connection to
> databases if business logic language is Java.
>
> I have worked so many projects using such architecture and performance
> has never been an issue.

Our experience differs. I've been a DBA for a very long time, I worked for a decent number of companies, both as a consultant and a resident DBA, in a permanent job and I've seen a lot of disastrous applications with ambition to be "database independent".

> For many software vendors it is more important
> to be able to support many databases without writing lot of database
> specific code than reducing performance times by taking advantage every
> proprietary feature of a database.

Such philosophy is usually a way to cut costs and compete on price instead of quality. There are only two databases used for storing important company data when it comes to the commercially viable companies: Oracle and UDB (formerly known as DB2). Unfortunately, developing applications for those two databases can be quite expensive proposition, because of the licensing, the underlying hardware and the people to maintain them. Small software companies decided to be smart and develop "database independent applications" on MySQL or SQL Server and declare them runnable on every standard database. Aside from the scalability issues (business logic on a WL server is nowhere near as scalable as one within the database) there are also numerous performance issues and idiotic query conditions which make queries impossible to optimize, like ":1 is null", a condition that I call "Hibernate was here", after the famous WWII joke saying that "Kilroy was here". The thing usually ends in endless performance problems, solved by acquiring bigger hardware and throwing money on the problem. Serge has mentioned Moore's Law and we are at the point when it is far too expensive to pay $100,000/year for a good DBA when it is possible to acquire significant processing power and storage capacity for $5000. That, unfortunately, is true only for very small companies. As soon as you add more then 50 concurrent users, 7x24 requirement, safety by replication and ensuring high availability by clustering, it becomes impossible to just buy another Dell PC and boost performance. These requirements are present in the fortune 50000, not just fortune 500. That, precisely, is the point of meltdown of "database independence" as a philosophy.
My prediction for the future is the following: when Larry grows too old for ruling Oracle with the iron fist, IBM will acquire it and we will have one database to rule them all, one database to find them, ...Database standards will be written in the Oracle manuals. Another version of the same ending is if Oracle buys IBM and then the resulting company buys the Catholic Church. That would probably bring Dan and Serge on the same page.
.

-- 
http://www.mladen-gogala.com