PL/SQL vs J2EE – Some comments…

 

Robert Vollman has a good post called PL/SQL vs J2EE. I started to write a comment, but it became too long so I decided to post is on my blog instead.

I’ll start by saying I have a very strong position on this point, as expressed in a previous post My Utopian Development Environment.

I’m ready to be shot down in flames by a J2EE guru, but this is what I observe when I look at our J2EE applications interacting with the database.

  • By far the most executed statement on our databases is “ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED”. This is executed in excess of 100,000 times an hour for one application alone. Why? Because every time the 10g application server does anything, it seems to send this first. Now this equates to the default operation in Oracle, so it is completely pointless. The developers didn’t know how to stop it so I raised a TAR with Oracle support and was told I couldn’t!
  • The way EJBs maintain there state is rather odd. When an EJB persists it’s state, using an insert or update, it immediately requeries it’s state. This turns a single operation into two database calls.
  • When the container detects a failure in a database transaction, it retries the transaction twice before bombing out with an error. I don’t know if this is configurable or not, but this is what I see.
  • Instantiating or persisting complex structures involves multiple database calls, each made individually with their own associated network traffic.

Now I’m not a J2EE developer and these observations are based on the applications I work with, but this does not add up to a scalable and performance orientated solution to data processing.

In addition, we have some data transfers that cannot go through the application layer (don’t ask!), so we have to duplication some of the J2EE business logic in PL/SQL. Same logic, two locations, disaster waiting to happen!

Googling around leads me to the conclusion that a lot of people are suffering the same problems as us. So what does this mean? Either J2EE just isn’t built for performance, or most J2EE developers don’t know how to do the job correctly. Either way, it’s like giving people a rope to hang themselves with.

My opinion? If you want it fast, put it in the database. If you want a single location for business logic, regardless of the applications accessing it, put it in the database.

Cheers

Tim…

Author: Tim...

DBA, Developer, Author, Trainer.

2 thoughts on “PL/SQL vs J2EE – Some comments…”

  1. 100% agree. One of the most important lessons I’ve learned switching from normal programming to programming with a database is to write “1 program” to get the data and another to process it. A lot of times, you’ll see things like:

    FOR c_val IN ( QUERY )
    LOOP
    SELECT x
    INTO v_blah
    FROM MyTbl
    WHERE idx = c_val.IDX;
    FOR c_val2 IN (
    SELECT *
    FROM MyTbl2
    WHERE idx1 = v_blah
    AND idx2 = c_val.IDX2
    )
    LOOP
    — STUFF;
    END LOOP;
    END LOOP;

    ————————-
    Or its equivalent in application code (e.g. java). If you’re mildly unlucky, they’re using bind variables, so its only 3 queries with 2 being run a bunch of times. If you’re very unlucky, there are no bind variables and you’ll be parsing like mad and wondering why its taking so long to process only 10000 rows.

Comments are closed.