Friday, July 8, 2016

Code in Application or the database?

My employer has a fairly large Delphi application with ~5 Million lines of pascal code.

They have a large database and use Oracle 12c to manage it.

I have seen many methodologies on how to manage your database access code.
On the edges I see two extremes are:

  • Do everything in Stored Procedures 
  • Do everything in Code outside the database, (Typical ORM)
I believe you need to use the best tool for the job.   So sometimes we are doing massive amounts of data processing that never leaves the database.     For that I use Packages/Stored Procedures in Oracle.     

Years ago we had a process that was 100% in code, and it took 20+ hours in code to execute, due to the round trips to the database.   When we looked at optimization, it was clear that network time was a huge part of the equation.    This Delphi process was converted to Oracle PL/SQL and was further optimized and our time was was reduced to <30 minutes

A simple example using the best tool for the job.    I have been a advocate of unit testing in Delphi.    Several months ago we had do some major PL/SQL work that resulted in about 50k lines of PL/SQL code.     Way too many lines of code to write and trust without sometime of unit testing framework.    We had been testing PL/SQL code from Delphi using DUnitX, but it was one too many steps away from the code under test.    I started working with utPLSQL a user testing framework for PL/SQL.   

In the past few weeks: I finished the move of utPLSQL from SourceForge to GitHub, and have released version 2.3.1 of utPLSQL.

Still love and use DUnitX but I use it from my Delphi Code not my PL/SQL code.

Regardless of the technology you find yourself using for development you need to unit test.

1 comment:

  1. A very good post thank you Robert. My team has migrated a number of database intensive routines to MS TSQL. We have also seen a big increase in performance of batch activities due to reduction of network round trips. Also we have seen increased code reuse and application reliability. When issues do arise the fixing of issues is much quicker.
    I accept not a fix for all challenges, but I am a big fan of a database centric application architecture.