ACCESS Procedure Reference

Creating and Using View Descriptors Efficiently

When creating or using view descriptors, follow these guidelines to minimize the use of CA-Datacom/DB and your operating system resources and to reduce the time CA-Datacom/DB takes to access data.

Select only the fields your program needs. Selecting unnecessary fields adds extra processing time.
Specify the order in which records are presented to SAS (with a SORT clause or a SAS BY statement) only if SAS needs the data in a particular order for subsequent processing.

The SAS BY statement issues an ordering clause to CA-Datacom/DB so that CA-Datacom/DB does the sorting using its system resources. This SORT clause overrides any existing SORT clause for the view descriptor. If you decide to use a SORT clause or a SAS BY statement, order by a key, which is indexed, when possible. (For help in determining which fields in a table are indexed, see your DBA or the table's creator.)

As an alternative to using a SORT clause, which consumes CPU time each time you access the CA-Datacom/DB table, you could use the SORT procedure with the OUT= option to create a sorted SAS data file. This is a better approach for data you want to use many times.
If a view descriptor describes a large CA-Datacom/DB table and you will use the view descriptor often, it might be more efficient to extract the data and place them in a SAS data file. (Even though the extracted data file will be very large, you will need to create it only once. Also, the extracted data will not reflect any subsequent updates to the table.) See Performance Considerations for more information about when it is best to extract data.
Specify selection criteria to retrieve a subset of the records CA-Datacom/DB software returns to SAS, where possible.
If you use a Default Key, the interface view engine will use an index read instead of a sort if it can. Index reads are faster, but not always possible. For example, an index read is not possible if you specify multiple sort keys, multiple WHERE clause conditions, or a WHERE clause condition with a column that is not a key.
Omit the KEY statement if you do not need a certain order and you want to retrieve the data sequentially. Otherwise, you might cause an unnecessary sort. PROC FSBROWSE, FSEDIT, and FSVIEW automatically use random access and require a value in the Default Key field.
You can provide your own URT that is fine-tuned for your applications.

Top of Page