Using LOAD-LOOKUP to Simplify Joins

Database tables often contain key columns, such as a product code or customer number. To retrieve a certain piece of information, you join two or more tables that contain the same column. For example, to obtain a product description, you can join the orderlines table with the products table by using the product_code column as the key.

With LOAD-LOOKUP, you can reduce the number of tables that are joined in one select. Use this command with LOOKUP commands.

The LOAD-LOOKUP command defines an array containing a set of keys and values, and loads it into memory. The LOOKUP command looks up a key in the array and returns the associated value. In some programs, this technique performs better than a conventional table join.

You can use LOAD-LOOKUP in the SETUP section or in a procedure. If used in the SETUP section, it is processed only once. If used in a procedure, it is processed each time that it is encountered.

LOAD-LOOKUP retrieves two fields from the database: the KEY field and the RETURN_VALUE field. Rows are ordered by KEY and stored in an array. The KEY field must be unique and contain no null values.

When the LOOKUP command is used, the array is searched (using a binary search) to find the RETURN_VALUE field corresponding to the KEY that is referenced in the lookup.

The following code example illustrates LOAD-LOOKUP and LOOKUP:

begin-setup
  load-lookup   
     name=prods 
      table=products
      key=product_code
      return_value=description
end-setup
...
begin-select
order_num (+1,1)
product_code
  lookup prods &product_code $desc
  print $desc (,15)
from orderlines
end-select

In this code example, the LOAD-LOOKUP command loads an array with the product_code and description columns from the products table. The lookup array is named prods. The product_code column is the key, and the description column is the return value. In the select paragraph, a LOOKUP on the prods array retrieves the description for each product_code. This technique eliminates the need to join the products table in the select.

If the orderlines and products tables were joined in the select (without LOAD-LOOKUP), the code would look like this:

begin-select
order_num (+1,1)
ordlines.product_code
description (,15)
from ordlines, products
where ordlines.product_code = products.product_code
end-select

Whether a database join or LOAD-LOOKUP is faster depends on the program. LOAD-LOOKUP improves performance when:

• It is used with multiple select paragraphs.

• It keeps the number of tables being joined from exceeding three or four.

• The number of entries in the LOAD-LOOKUP table is small compared with the number of rows in the select, and they are used often.

• Most entries in the LOAD-LOOKUP table are used.