Kbase P25829: Dynamics and Array Fields.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
20/04/2007 |
|
Status: Unverified
GOAL:
Dynamics and Array Fields.
FACT(s) (Environment):
Dynamics 2.0A
FIX:
Avoid Array Fields
One particular characteristic of the Progress database not shared by SQL, or by most other modern database types, is the array field. The best rule where arrays are concerned is simply never use them in your database definition. Array fields can be useful in some circumstances in a temp-table definition where you need
something to function as a set of ?registers? for the accumulation of large numbers of related data items or some such purpose. In your database itself, the use of array fields is almost always a mistake. If you are using array fields to try to increase performance by reducing the number of related records you need to read to calculate some information, you are likely miscalculating the actual cost or using the wrong technique.
Consider that array fields are inherently non-normalized ? they attempt to represent a one-to-many relationship in a single record. Arriving at the appropriate extent for an array is often an arbitrary decision, and if the original extent turns out to be insufficient at some point in the future, you have to make a schema change (and most likely a change to your application code) to deal with this issue. Arrays can lead to large records that span multiple database blocks, increasing the number of reads required to retrieve a single
record, which reduces any performance gain you might have thought you were getting by using them.
Transporting such large records across a network requires increased bandwidth, so they?re bad for performance in that regard also. Array values cannot be efficiently indexed, cannot be used as a join field, and cannot be accessed using SQL syntax (for example, from a reporting tool that will want to report on the
data those array fields hold). They cannot be filtered on; that is, you can?t write a single query statement to retrieve records where one of the values in an array field matches or is greater than or less than some value.
These are all good reasons to avoid arrays.
If you must disregard this guideline, then be advised to define an array only for data that is accessed strictly in your business logic (on the server-side of your application only) for some very particular purpose. Don?t ever expect to display array data using standard Dynamics components, manipulate it on the client, sort or filter on it, report on it, or use it in any other way outside the server-side procedure where it is manipulated.
If you define a SmartDataObject? for a table with an array field, the array will be expanded into a series of discrete fields for each element. This will likely not result in the kind of client-side representation that you want for the data. It can easily cause the DEFINE statement that defines the temp-table used to pass data back and forth between client and server to exceed the maximum length of a Progress 4GL statement, effectively making this table unusable with Dynamics. No apology is made for this, and there are no plans to compensate for this limitation.