Let’s do it in Java ...


Print this article  Send the URL to a friend

Environment

Competent Java developers that have hardly any knowledge of DBMS products and of SQL have been let loose on a financial application. Very soon, they had important memory consumption issues.

Code

Nosing around in the code gave the clue: a large number of simple SELECT statements were used to fetch data from tables, and this data was stored into Java structures that were allocated on the fly.

Once the data was safely stored in the Java structures, serious work could start:


       // check to see if entry exists
       TripleKey tripleKey = new TripleKey(NotPaidStruct.pid_deal,
                                           NotPaidStruct.cycle_rid,
                                           NotPaidStruct.expense_code);
       if (tripleKeyValues.containsKey(tripleKey))
       {
        AppCache0.MyStruct UnpaidStruct = (AppCache0.MyStruct)tripleKeyValues.get(tripleKey);
        UnpaidStruct.UnpaidAmount += NotPaidStruct.UnpaidAmount;
       }
       else
       {
           tripleKeyValues.put(tripleKey, NotPaidStruct);
           AppCache1.addNotPaid(NotPaidStruct);
           AppCache1.addOutstandingNotPaid(NotPaidStruct);
       }

If this isn’t an aggregation, then it’s a very good imitation. It begs the question: why does the query return all rows instead of performing a simple

select pid_deal, cycle_rid, expense_code, sum(accrual_amount)
from ...
group by pid_deal, cycle_rid, expense_code
?

Some parts of the Java code may require structures with some level of detail, but most queries have been built on the same pattern, including at many places where one is only interested in the aggregate result.

We have no doubt that Java wizards can perform data operations more efficiently than a widely used C database engine that has been slowly improved over the course of more than a quarter of a century. It would indeed be shameful to write a ten-line SQL statement when you can write a full page of Java code to do the very same thing (it gives a better feeling of control, doesn’t it). But, forgetting about efficiency, one might think that given the price of Oracle licenses, the more you do in the SQL code the better ...

Copyright © RoughSea Ltd, 2006