4 Selecting a SAS/ACCESS Method Chapter 1
The ACCESS and DBLOAD procedures support indirect access to DBMS data.
These procedures are no longer the recommended method for accessing DBMS
data, but they continue to be supported for the database systems and
environments on which they were available in Version 6.
See “Selecting a SAS/ACCESS Method” on page 4 for information about when to use
each method.
Note: Not all SAS/ACCESS interfaces support all of these features. See the chapter
on features by host to determine which features are available in your environment.
Selecting a SAS/ACCESS Method
In SAS/ACCESS software, there are often several ways to complete a task. For
example, you can access DBMS tables and views by using the SAS/ACCESS LIBNAME
statement or the Pass-Through Facility. The advantages and limitations of these
features are described below. Before processing complex or data-intensive operations,
you might want to test several of these features to determine the most efficient feature
for your particular task.
It is generally recommended that you use the SAS/ACCESS LIBNAME statement to
access your DBMS data because this is usually the fastest and most direct method. An
exception to this is when you need to use non-ANSI standard SQL. ANSI standard SQL
is required when using a SAS/ACCESS LIBNAME engine in the SQL procedure, but
the Pass-Through Facility accepts all the extensions to SQL that are provided by your
DBMS.
The SAS/ACCESS LIBNAME statement has the following advantages:
Significantly fewer lines of SAS code are required to perform operations on your
DBMS. For example, a single LIBNAME statement establishes a connection to
your DBMS, enables you to specify how your data is processed, and enables you to
easily browse your DBMS tables in SAS.
You do not need to know the SQL language of your DBMS in order to access and
manipulate data on your DBMS. You can use SAS procedures, such as PROC SQL,
or DATA step programming on any libref that references DBMS data. You can
read, insert, update, delete, and append data, as well as create and drop DBMS
tables by using normal SAS syntax.
The LIBNAME statement provides more control over DBMS operations such as
locking, spooling, and data type conversion through the many LIBNAME options
and data set options.
The LIBNAME engine can optimize the processing of joins and WHERE clauses by
passing these operations directly to the DBMS. This takes advantage of your
DBMS’s indexing and other processing capabilities. For more information, see
“Overview of Optimizing Your SQL Usage” on page 33.
The LIBNAME engine can pass some functions directly to the DBMS for
processing.
The Pass-Through Facility has the following advantages:
Pass-Through Facility statements enable the DBMS to optimize queries,
particularly when you join tables. The DBMS optimizer can take advantage of
indexes on DBMS columns to process a query more quickly and efficiently.
Pass-Through Facility statements enable the DBMS to optimize queries when the
queries have summary functions (such as AVG and COUNT), GROUP BY clauses,
or columns created by expressions (such as the COMPUTED function). The DBMS
optimizer can use indexes on DBMS columns to process the queries more quickly.
