Number: TB0005

Availability: As of BuildPro 8.00

Introduction

When migrating applications from using ISAM file structures (C-ISAM on Linux) to using relational databases (such as Oracle or SQL Server) it is important to understand the basic differences in how these systems work. This is especially important to increase performance after the migration to a relational database. ISAM file structures are simple and are accessed via direct calls to routines to read, write or modify a specific record. The ISAM system maintains a series of indexes which allows developers more efficient access to groups of records by ordering them by an index key. Each access to the database file is very quick.

Relational databases are accessed via a structured query language (SQL) which tells a database engine what task to perform. SQL calls can be as simple as reading a single record or as advanced as modifying 1,000s of records in several different database tables. Each access to the database causes more overhead than accessing an ISAM file, but there is a trade-off since a single access can do more than one task.
 

Problem

When creating TODAY applications for use with ISAM file structures, program code is written to perform every single record transaction. This use of many data or file transactions will cause significant overhead in relational databases, causing high utilisation of computer resources or overall poor performance (before making structural changes to your application, evaluate the cost of upgrading hardware as an option).

When writing TODAY applications for relational databases, developers use the TODAY SQL command instead of the FILE command when many records need to be read, inserted or modified.

The following is an example of ISAM type code for reading many records:

FILE *FIRST trans
FOR *P01 = 1 ; *P01 <= 1000 ; *P01 = *P01 + 1
FILE *NEXT trans
ENDFOR

* ) A total of 1,001 data transactions are performed to read the first 1,000 records.

The following is an example of SQL type code for reading many records:

SQL *SELECT trans_sel WHERE trans_date = "01/01/09"

* ) A single data transaction is performed to read all transaction records for Jan 1st, 2009.

Solution.

It is normally not feasible for developers to rewrite the many database calls in an existing application. To address any performance issues it is most often adequate to address only specific parts of the application. These modifications can reduce the overhead and also remove potential bottle necks in the distribution of system resources.

Use the following guideline when looking for specific parts of an application to address (in priority):

1. Commonly used read functions that search tables for multiple records (lookup functions, lists etc). These can normally be rewritten and emulate the exact original functionality. This will provide a direct and visible performance enhancement.
    
2. Batch oriented updates. If a batch process updates many records in different tables, the procedure can normally be written as single SQL statement to perform the whole job in one database call.
    
3. Reports. Reports that require a large number of record selections will be performed much quicker if the initial selection is performed through a single SQL call.

It is often worth implementing these enhancements in phases as the first set of enhancements may solve all the immediate performance issues.