APPX is the Premier Development and Runtime Environment for Business Application Software
(Answer) (Category) FAQ's - APPX Software, Inc. : (Category) APPX Utility : (Category) APPX Development Environment : (Category) Data Dictionary :
Do many duplicate keys decrease the performance?
When you define a key (in the Appx data dictionary), you can choose from four key types:
    Primary
    Alternate/Unique
    Alternate
    Alternate/Forced
The last two types allow duplicate key values. When you create an Alternate/Forced key, Appx appends the primary key (for each record) to the end of the Alternate/Forced key.

There are two advantages to an Alternate/Forced key (and at least two disadvantages).

The first advantage is that you don't suffer the performance penalty usually associated with a long chain of duplicate key values (because you have no duplicate key values). The second (and typically more important) advantage is that, when you read along the Alternate/Forced key path, you get duplicate records in primary key order. Many applications assume that duplicate key values appear in primary key order.

The first problem with an Alternate/Forced key is that it takes more disk space to store the index and, therefore, more disk I/O to read and write the index - Appx must store the complete primary key for every record in the alternate index (along with the key segments that define the Alternate/Forced key). The second problem with an Alternate/Forced key comes from the fact that Appx limits keys to 16 segments - if you have a four-segment primary key, you can't define more than 12 segments in an Alternate/Forced key. That's not often a problem, but it can be difficult to work around when you run into it. Appx will coalesce adjacent string segments into a single segment to reduce the segment count so you run into the 16 segment limit less often than you might think.

Note that the on-line help text for Key Type states that an "Alternate/Forced key is identical to an Alternate key fields, except that duplicate alternate keys are retrieved in primary key order" - there's no guarantee there that you'll gain better performance by choosing an Alternate/Forced, we just promise to return records with duplicate key values in a specific order. The mechanisms that we use may (and in fact do) vary by file type (AppxIO vs. Oracle vs. DB2, etc.
It just so happens that an Alternate/Forced key performs better than an Alternate key (at least for AppxIO files). In fact, AppxIO treats every Alternate key the same way it treats Alternate/Forced keys. Appx may choose a different method to improve the performance of duplicate indexes in the future - so, if your application requires that duplicate key values appear in primary key order, be sure to choose Alternate/Forced: you'll get record in the order that you expect and you'll get better performance (in most cases).

[Append to This Answer]
2005-Jun-01 9:22am
Previous: (Answer) How many times does Post-PCF Read FLEP fire?
Next: (Answer) Can ERROR statement be used in FLEP's?
This document is: http://board.appx.com/cgi-bin/fom.cgi?file=574
[Search] [Appearance]
This is a Faq-O-Matic 2.719.
Copyright 2003 by APPX Software, Inc. All rights reserved.