ACCESS Procedure Reference

Creating and Using View Descriptors Efficiently

To efficiently use SYSTEM 2000 and operating system resources:

Select only the items your program needs. Selecting unnecessary items adds extra processing time.
Use an ordering-clause or a BY statement in SAS to specify the order in which logical entries are presented to SAS only if SAS needs the data in a specific order for processing. (The BY statement in SAS issues an ordering-clause to SYSTEM 2000 and overrides any existing ordering-clause for the view descriptor.) If you use an ordering-clause or a BY statement in SAS, sort by an indexed item when possible.
As an alternative to using an ordering-clause, which consumes CPU time each time you access the SYSTEM 2000 database, you can use the SORT procedure with the OUT= option to create a sorted SAS data file. This is a better approach for data that you want to use multiple times.
If a view descriptor describes a large SYSTEM 2000 database and you will use the view descriptor often, it might be more efficient to extract the data and place it in a SAS data file. (Although the extracted data file will be very large it is created only one time. However, the extracted data will not reflect any subsequent updates to the database.)
When possible, specify selection criteria to subset the number of logical entries that SYSTEM 2000 returns to SAS.
Write selection criteria that enable SYSTEM 2000 to use available indexes when possible. This applies whether you specify the selection criteria as part of the view descriptor or use a WHERE clause in SAS. This is especially important when accessing large databases because when SYSTEM 2000 cannot use an index, it scans the entire database sequentially.

For more information about where-clause optimization guidelines, see QUEST Language and System-Wide Commands, Version 12.

Top of Page