News, views, tips and tricks on Oracle and other fun stuff

Database as a Storage (DBaaS) vs. Thick Database

A recent addition to my Oracle PL/SQL library is the book Oracle PL/SQL Performance Tuning Tips & Techniques by Michael Rosenblum and Dr. Paul Dorsey.

I agree with Steven Feuerstein’s review that “if you write PL/SQL or are responsible for tuning the PL/SQL code written by someone else, this book will give you a broader, deeper set of tools with which to achieve PL/SQL success”.

In the foreword of the book, Bryn Llewellyn writes:

The database module should be exposed by a PL/SQL API. And the details of the names and structures of the tables, and the SQL that manipulates them, should be securely hidden from the application server module. This paradigm is sometimes known as “thick database.” It sets the context for the discussion of when to use SQL and when to use PL/SQL. The only kind of SQL statement that the application server may issue is a PL/SQL anonymous block that invokes one of the API’s subprograms.

I subscribe to the thick database paradigm. The implementation details of how a transaction is processed and where the data is stored in the database should be hidden behind PL/SQL APIs. Java developers do not have to know how the data is manipulated or the tables where the data is persisted, they just have to call the API.

However, like Bryn, I have seen many projects where all calls to the database are implemented as SQL statements that directly manipulate the application’s database tables. The manipulation is usually done via an ORM framework such as Hibernate.

In the book, the authors share a particularly bad example of this design. A single request from a client machine generated 60,000 round-trips from the application server to the database. They explain the reason behind this large number:

Java developers who think of the database as nothing more than a place to store persistent copies of their classes use Getters and Setters to retrieve and/or update individual attributes of objects. This type of development can generate a round-trip for every attribute of every object in the database. This means that inserting a row into a table with 100 columns results in a single INSERT followed by 99 UPDATE statements. Retrieving this record from the database then requires 100 independent queries. In the application server.

Wow! That’s bad. Multiply this by a 100 concurrent requests and users will start complaining about a “slow database”. NoSQL to the rescue!

Filed in Oracle on 18 Aug 14 | Tags: , ,

Reader's Comments

  1. |

    I’ve worked with systems built with Hibernate. I’m not saying that it always works with the database the best way possible, but it certainly doesn’t do anything as stupid as in your example.

    As for using API built with PL/SQL to update the database – unfortunately it is a lot easier to get an ORM to generate the correct DML for updating tables than to run the stored procedures of an API. The better Java IDEs can read the data dictionary and get the structures of your tables. Most relational DBMSs follow the ANSI standard for catalog tables/views. But stored procedures depend highly on the particular RDBMS.

    • |

      Hi John, it’s not Hibernate that’s doing stupid things, it’s the inexperienced programmers who use it and who treat the database like a flat file system. Unfortunately going for easy is not necessarily the right thing to do. It may be easy now, but the tech debt you may incur down the road will be a lot harder to fix. Cheers!