Progress
Database Administration
Guide and Reference


Optimizing Data For Random Access

If your application accesses data in a random order, for example an online transaction-processing application, you can dump and reload the data to optimize it for random access. Use a database with small, fixed-length extents spread across the volumes to balance the I/O across the disks. Use several clients to load data simultaneously, thereby spreading the data across all disks.

This technique also improves load performance on SMP platforms.

Follow these steps:

  1. Dump the data and definitions.
  2. Load the definitions using the Data Dictionary or the Data Administration tool.
  3. In multi-user mode, start a server with a before-image writer (BIW) and asynchronous page writer (APW).
  4. Start a client for each processor on your system. Have each client load certain tables. For each client, write a Progress 4GL procedure similar to the following:
  5. /*Run this procedure after connecting to a database*/ 
    DEFINE VAR RECCOUNT AS INT NO-UNDO. 
    DEFINE VAR NXT-STOP AS INT NO-UNDO. 
    DEFINE VAR i as INT NO-UNDO. 
    INPUT FROM tablename.d 
    TOP: 
    REPEAT TRANSACTION: 
      NXT-STOP=RECCOUNT + 100 
      REPEAT FOR table-name WHILE RECCOUNT < NXT-STOP 
      ON ERROR UNDO, NEXT ON ENDKEY UNDO, LEAVE TOP: 
        CREATE table-name. 
        IMPORT table-name. 
        RECCOUNT = RECCOUNT + 1. 
      END. 
    END. 
    

    The clients, loading the data simultaneously, distribute the data across all disks. This eliminates hot spots (that is, areas where data might be concentrated).

  6. After the data is loaded, perform a full index rebuild. Use the PROUTIL IDXBUILD utility.

Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095