Previous Page | Next Page

Performance Considerations

Temporary Table Support for SAS/ACCESS

Overview

DBMS temporary table support in SAS consists of the ability to retain DBMS temporary tables from one SAS step to the next. This ability is a result of establishing a SAS connection to the DBMS that persists across multiple SAS procedures and DATA steps.

Temporary table support is available for these DBMSs.

The value of DBMS temporary table support in SAS is increased performance potential. By pushing processing to the DBMS in certain situations, you can achieve an overall performance gain. These processes provide a general outline of how to use DBMS temporary tables.

General Temporary Table Use

Follow these steps to use temporary tables on the DBMS.

  1. Establish a global connection to the DBMS that persists across SAS procedure and DATA step boundaries.

  2. Create a DBMS temporary table and load it with data.

  3. Use the DBMS temporary table with SAS.

Closing the global connection causes the DBMS temporary table to close as well.

Pushing Heterogeneous Joins

Follow these steps to push heterogeneous joins to the DBMS.

  1. Establish a global connection to the DBMS that persists across SAS procedure and DATA step boundaries.

  2. Create a DBMS temporary table and load it with data.

  3. Perform a join on the DBMS using the DBMS temporary and DBMS permanent tables.

  4. Process the result of the join with SAS.

Pushing Updates

Follow these steps to push updates (process transactions) to the DBMS.

  1. Establish a global connection to the DBMS that persists across SAS procedure and DATA step boundaries.

  2. Create a DBMS temporary table and load it with data.

  3. Issue SQL that uses values in the temporary table to process against the production table.

  4. Process the updated DBMS tables with SAS.

Although these processing scenarios are purposely generic, they apply to each DBMS that supports temporary tables. For details, see the DBMSTEMP= LIBNAME Option.

Previous Page | Next Page | Top of Page

Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.