More Architecture Mistakes To Avoid, Database Design Mistakes To Avoid, Tuning
When I started working with Oracle, I helped to replace a Cobol system. For a while, the company systems ran half on Cobol, and half on Oracle. The Oracle 7 system was pretty slow then. After the cold backups had run overnight, it was not unusual in the morning to see a query against a 700K row table take about 30 seconds.
I was asked to write a C program that only interfaced with the Cobol flat files, and read Cobol transaction records. Each transaction was stored in multiple rows, and given repeating groups of records, could vary in size from a few rows, to dozens of rows. The files contained about 700K to 800K rows altogether. The program grabbed information out of the multiple lines, tallied the numbers up, and output one line of summary. Lots of IF statements, and loops. About 800 lines of code.
I expected this program to take at least 30 seconds, given that database queries to similar size tables took that long, and that there was so much logic and so many lines of code.
In fact, the program ran in about 5 seconds! I was really surprised.
But when you think of it, it should be obvious that there are many things multitasking under the covers when you run a SQL statement in Oracle. Rights and roles are being checked. Checks against the primary key. Foreign Key lookups. Check constraints. Indexes. The intricacies of the optimizer. Transaction control. Rollback segments. Locking. Logging. Commit, or rollback. Perhaps auditing is turned on. It’s easy to see when you query the Oracle data dictionary to see what SQL statements are in progress.
But when you run a dedicated self contained C, or Cobol program, there is little else going on in the background. Because of this, such a program will actually run MUCH faster than Oracle.
The point being. So many systems I’ve seen running on Oracle, are basically Cobol systems. They just happen to be running on a database, not a file system. The Cobol “files”, are put into database “tables”.
The people who “designed” such systems clearly did not understand database theory or paradigms. They were “programmers”. Not “Data Architects”.
If you want to make a Cobol system, write your system with flat files and use Cobol. Or C. The system will run much faster. You will have all the freedom in the world to do just whatever you like. You will not be bothered with having to learn database concepts. Or database design, and designing table “structures”. With Cobol, you can actually change the file format and contents from row to row! Or primary and foreign keys. You can write your own locking routines. And indexing routines. Security routines. You will have all the opportunity to completely recreate many wheels and show off how smart you are. And the licensing fee will be much, much lower.
If you aren’t designing tables, and using primary keys and foreign keys, the way Codd and Date wrote about them, you most probably have a Cobol system. It just happens to reside inside a database.
Learn the database paradigm and use it.