Information for the Database Administrator

Maximizing the CA-Datacom/DB Interface Performance

Among the factors that affect the interface performance are the size of the table being accessed, the number of fields being accessed, and the number of data records qualified by the selection criteria. For tables that have many fields and many data records, you should evaluate all SAS programs that need to access the table directly. In your evaluation, consider the following questions:

Does the program need all the CA-Datacom/DB fields? If not, create and use an appropriate view descriptor that includes only the fields needed.
Do the selection criteria retrieve only those data records needed for subsequent analysis? If not, specify different conditions so that the selected records are restricted for the program being used.
Is the data going to be used by more than one procedure in one SAS session? If so, consider extracting the data and placing it in a SAS data file for SAS procedures to use, instead of enabling the data to be accessed directly by each procedure. See Performance Considerations for circumstances in which extracting data is the more efficient method.
Do the records need to be in a particular order? If so, include a SORT clause in the appropriate view descriptors or a SAS BY clause in a SAS program.
Do the selection criteria enable CA-Datacom/DB to use key (indexed) fields and non-indexed fields efficiently? See WHERE Clause in a View Descriptor for some guidelines on specifying efficient selection criteria.
Does your WHERE clause cause CA-Datacom/DB to create a temporary index, which often requires excessive processing time? For more information about displaying WHERE clauses and messages about creating temporary indexes, see DDBTRACE= Data Set Option.
What kind of locking mechanism is required? (See Locks and the Spool Files.)
Are your CA-DATADICTIONARY elements well-matched to your view descriptors?
Would a different URT help?
Would use of a default key give you a faster result?

Top of Page