Monday, November 17, 2008

One last hurrah on database independence

I had mentioned in my previous post on database independence about Toon Koppelaars presentation on a database centric approach to application development. I contacted him and he said he was going to post his presentation on his site soon. But in the meantime, I thought I'd show one of his slides which I think speaks volumes to the performance aspects of trying to keep with a database independent approach.

The slide shows:
a) If you put business logic in the mid tier, you’ll call the dbms an order of magnitude more.
b) If you also put data logic in the mid tier, you’ll call the dbms two orders of magnitude more.
c) Finally, if you also don’t use SQL effectively, you’ll call the dbms three orders of magnitudes more.

So, a single click on a "Save" button by the user can ultimately mean 100 calls the dbms has to execute to handle all the middle tier madness!

You should be able to download Toon's full presentation from very soon. Give it a's good stuff.

1 comment:

Wollaton DBA said...

Hi Karen,

To say that putting the business logic in the middle tier generates an in-ordinate amount of SQL calls depends on how an application has been designed. Specifically, are you using:-

1. Stored procedures
2. JDBC and / or Oracle JDBC extension statement batching ?.
3. middle tier caching techniques.
4. Using a caching technology that sits above the database but below the middle tier ?, coherence is one example of a product that facilitates this.
5. Exploiting Oracles array interface
6. Tuning the JDBC pre-fetch size to minimize network round trips.

As a general rule of thumb, processing should always be carried out closest to the resource that requires it. This is one of the best "designing for performance" patterns in the IT industry, as used with ExaData smart scan technology. I have seen instances on projects where stored procedures are called over and over and over again to validate data read in from files by Java against data cached in the middle tier, thus causing death by network round trips. Conversely, I have seen Java programmers attempt to do in Java what rich and mature functionality in the RDBMS already provides.

Some lateral thinking needs to be applied here. I would not necessarily advocate putting all the business logic in the database nor would I advocate putting this all in the middle tier and using Oracle as the "black box" that Tom Kyte often refers to.