Informix 4GL Programming Tips

Published on July 1, 1994 by Lester Knutsen

First published in the Washington Area Informix User Group Newsletter
Volume 4, No. 3 - July 1994

1. Only select columns that you really need

When you select data, it goes from disk to memory.  Ten un-needed bytes of data from 10,00 rows translates into reading 100,000 bytes unnecessarily.  Avoid "SELECT *" statements and only select the fields you will be using in your program.  Some of the biggest performance gains I have seen have come from eliminating unused fields from select statements.  In a network environment, this will also reduce network traffic.

2. Only select rows when they are really needed

Several programs I have done performance reviews on, started by selecting all the rows in a table, and making the user wait while this select was occurring.  Even if the user only wants to locate a few records, they have to wait until all records are found.  Avoid select statements without a where clause, and statements that select rows a user may not need.

3. Use the dual cursor model for selecting and updating data

One approach is to use a dual cursor model.  The first cursor selects all the rowids a user may need, and as the user picks the next row, a second cursor selects the columns of data for that row.  I have an example program at the end of the article that uses the dual cursor model.

4. Only update the columns that need to change

We want to avoid transferring unnecessary bytes between memory and disk.   Only update the columns that need to change.  Avoid updating all columns unnecessarily, for example, using "set tab.* = rec.*".  Also, avoid updating columns with indexes,  as the data has to be written to disk and the index updated, requiring two or more writes to disk.

5. Use  "UPDATE WHERE CURRENT OF" syntax

When you are updating a row, you most likely have already selected the row and loaded it in memory.  The statement "update table set X=X where rowid = rec.rowid" re-selects the data from disk again before updating it.  Use the statement "update where current of" to update a row you have already selected.  This will speed up your updates.  See the example program at the end of this article.

6. Use of direct updates vs. selecting data for update

There was a very interesting discussion on the Internet in comp.database.informix on the fastest way to update 2 million rows.   The general consensus was to turn off logging and let the database engine do the update for the fastest results.  I did some tests, and this was 5 to 6 times faster than selecting the data and then updating it using the dual cursor method.

7. Using LET vs. INITIALIZE

The INITIALIZE statement performs a subroutine, which is slower than the LET statement, which performs a byte copy.  At the beginning of a program, I like to initialize a null record (null_rec.*).  Then in the program, use "LET p_rec.* = null_rec.*".  This is faster than calling INITIALIZE repeatedly.

8. Using GLOBAL variables vs. passing parameters

Passing variables to functions require push and pop functions on the 4GL stack.   Look at the C code generated by 4GL and examine what happens when you pass a variable to a function.  There is some extra overhead in this.  I recommend carefully selecting variables that are passed frequently to functions and making them GLOBAL variables.  GLOBAL variables are accessed directly in memory.

9. Program initialization functions

Avoid repeatedly opening and closing the same forms, preparing static cursors, and initialing NULL variables throughout a program.   At the beginning of a program, have a function to perform all initialization of null variables, opening forms, and preparing cursors.

10. Update statistics

The database optimizer needs correct information about what is in your database to perform well.  After adding or deleting many rows in a 4GL program, use the statement "UPDATE STATISTICS FOR table_name" to update the information the optimizer uses.

11. Plan your locking method

Row locking allows many people to update data at the same time.   However, it can consume system resources and may fail if your system does not have enough locks available.  If users are updating single rows, then row-level locking works best.  However, for batch updates, consider locking the entire table.  One rule I have used is if the update will involve more than 200 rows or 25% of the rows, the table needs to be locked.

12. Plan transaction length

Avoid long processes that may fill up your logs and lock the database engine.   This is one advantage of the dual cursor model vs. the direct update model.  The direct update model is one long transaction and may fill up your logs.  Using a cursor and committing after each row is updated will ensure that your logs do not fill up.

Figure 1. Example Program Using the Dual Cursor Model

######################################################################
#           Copyright 1994 Advanced DataTools Corporation
# Function:     updcursor.4gl
# Author:       Lester B. Knutsen
# Description: A program to demo the dual cursor approach
######################################################################

database stores

globals
    define     price           like items.total_price,
          new_price       like items.total_price,
          rowno           integer
end globals

main

## Declare the big cursor selecting only the rowid or a unique indexed field for all rows required.

prepare pc_select_all from " select rowid from items "
declare c_select_all cursor with hold for pc_select_all

## The with hold option will keep the cursor open after a commit work
## Declare the small update cursor to lock one row at a time - locking the smallest data possible.

prepare pc_lock from "select total_price from items where rowid = ? for update of total_price"
declare c_lock cursor for pc_lock 

## Prepare the update statement to update the current row in memory.

prepare pc_upd from "update items set ( total_price ) = ( ? ) where current of c_lock "

## Fetch the Big cursor.

foreach c_select_all into rowno, price
## On every row - fetch the small cursor.  This will lock and update one row at a time

    begin work     ## Used when database logging is turned on 
    open c_lock using rowno
    fetch c_lock into price
          let new_price = price * 1.1
          execute pc_upd using new_price
          display "Update from ", price, " to ", new_price
    commit work  ## Used when database logging is turned on 

## Need to add error checking for failed updates and rollback work
end foreach

display "Update complete "
display ""
end main