Progress
Programming
Handbook


Choosing Between Sequences and Control Tables

Sequences are objects, like fields, that you can create and maintain in any Progress database. Unlike fields, sequences reside in a separate database block independent of application tables. Figure 9–7 shows the basic relationship between database fields and sequences.

Figure 9–7: Database Fields and Sequences Comparison

Each sequence has a name and a value, similar to a field. But unlike a field, a sequence also has different schema attributes that determine how the sequence value can change. These include:

Progress provides a dedicated set of 4GL functions and statements to access and increment sequences strictly according to the defined attributes.

Sequences vs. Control Tables

Before Progress Version 7, the only way Progress could generate sequential values was to maintain integer fields in a table record created specifically for this purpose. This is often done in a control table, separate from all other application tables.

For example, you might have a control table named syscontrol that contains the field last–cus–num. This field holds the value of the Cust–num field for the last Customer record. The following code fragment generates a new Customer record with a unique customer number:

. . . 
DEFINE VARIABLE next-cust-num   LIKE Customer.Cust-num. 
DO FOR syscontrol: 
   DO TRANSACTION: 
      FIND FIRST syscontrol EXCLUSIVE-LOCK. 
      next-cust-num = syscontrol.last-cus-num + 1. 
      syscontrol.last-cus-num = next-cust-num. 
   END. /* transaction */ 
   RELEASE syscontrol. 
END. 
DO TRANSACTION: 
   CREATE Customer. 
   Customer.Cust-num = next-cust-num. 
   DISPLAY Customer. 
   UPDATE Customer EXCEPT Cust-num. 
END. /* transaction */ 
. . . 

Note that access to the syscontrol table must be made within a small transaction to avoid lock contention problems.

Sequences provide a built-in means for generating incremental values, but they are not suitable for all applications. In certain situations (described in the following sections), you might need to use a control table, instead.

Table 9–7 compares sequences and control tables.

Table 9–7: Comparison of Sequences and Control Tables 
Capability
Sequences
Control Tables
Access speed
Fast
Slow
Transaction independent
Yes
No
Guaranteed order
Yes
No
Auto initializing
Yes
No
Auto cycling
Yes
No
Bounds checking
Yes
No
Database limit
100
Field limit

Performance vs. Capabilities

In general, sequences provide a much faster and more automated mechanism to generate sequential values than control tables. Sequences are faster because their values are all stored together in a single, table-independent, database block, and they do not participate in transactions. As a result, the principal limitations of sequences for certain applications include:

Transaction Independence

Transaction independence guarantees that each subsequent sequence value is incremented (positively or negatively) beyond its previous value, but does not guarantee the extent of the increment. In other words, with sequences you can have incremental gaps in the sequential values actually used in your application. These gaps result when sequences are incremented during a transaction that is subsequently undone, leaving the sequences set to their latest values. If the transaction restarts, it uses these latest values, not those generated for the previously undone transaction. Figure 9–8 shows how two overlapping transactions with rollback can create records and commit consecutive sequence values with gaps.

Figure 9–8: Sequences and Overlapping Transactions

Both transactions start out with the sequence set to 1, which has already been used in the database. Transaction T1 increments the sequence first, assigns, and then rolls back leaving the sequence value at 2. Then transaction T2 increments, assigns, and commits the sequence value 3 to a database field. Transaction T1 then increments, assigns, and rolls back a second time, leaving the sequence value at 4. Finally, transaction T1 increments, assigns, and commits the sequence value 5 to the same database field in a different record. Thus, the sequence values 2 and 4 are skipped and never used in the database. All subsequent transactions increment and commit sequence values greater than 5.

NOTE: Transaction independence is provided for standard sequence increment/decrement operations, but not for operations that set the sequence value directly. Setting the value directly is a special operation intended only for maintenance purposes. For more information, see the "Using the CURRENT–VALUE Statement" section.

Control tables, however, obey the same transaction rules as any other table, and can ensure that their field values are reset for undone transactions. If it is essential to generate sequence values without incremental gaps, you must use a control table rather than a sequence.

Storage Limits

If you need to maintain more than 100 sequence objects at a time, you must either add another database to define more sequences or use a control table to generate incremental values in your application. The number of incremental fields you can maintain in a control table is limited only by the number of integer fields you can store in the table.


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