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

On 25.05.2007 00:43, zigzagdna_at_yahoo.com wrote:
> On May 24, 2:39 pm, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net>
> wrote:

>> Unfortunately, this is precisely the thing frequently generated by one of
>> the most popular tools "for writing database independent applications",
>> namely Hibernate. Hibernate helps you create applications which will suck
>> regardless of the underlying platform.

Unfortunately I have to agree - so far my experience with Hibernate with regard to performance and even implementation logic was dissatisfying.

Few years ago I would have subscribed to the statement that it's a good idea to write DB independent applications because it will increase your chances of selling them if you are able to deploy it in different corporate environments with different DB products used. I have learned along the course that this is nice in theory but does not work in practice. Instead, today I would say it is a good idea to write an application that supports different DB vendors (for the stated reasons) but it cannot be done in a DB agnostic way. How much effort this is depends of course on the application at hand but there is always a price to pay - either in terms of development efforts, reduced number of DB vendors supported, performance of the application or features.

> 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. Business/
> Application logic is where all the logic/algorthsm etc are
> implemented. Business logic calls database to get data and update/
> insert/delete data. Thus database code is little.

That's the theory. But it will leave you with a dissatisfactory situation because

• if you do not implement business rules for data integrity in the DB, you might actually end up with inconsistent data in cases of application failure
• if you *do*, you have redundant business rules
• if you use a DB just as a huge bitbucket you are wasting resources (you would not need to spend the money and resources for using the DB) and you have to reimplement quite a few features on the application level (transactions, indexing etc.) - even if it's done in an application server, it is redundant.

> Using OO techniques,
> it is not all that difficult to write database classes in such as
> fashion that they are mostly database independent.

... and mostly imperformant. From my experience vanilla approaches to storing a bunch of classes in a RDBMS lead to bad performance if not other problems.

> 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
> propritery language.

Funny thing is: by using stored procedures you might actually be able to make application code /more DB independent/.

> Business logic can run on same server as
> database server

Which is generally a bad idea. DB servers should be dedicated most of the time (i.e. larger than toy example size DB).

> then network time is reduced drastically. 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. 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 proprietery feature of a database.

 From my experience you can do that as long as you are not dealing with a significant volume or load. Things change quickly when volume and load increase.

Kind regards

        robert Received on Fri May 25 2007 - 04:17:23 CDT