Previous Page | Next Page

Optimizing Data Storage

Setting LIBNAME Options That Affect Performance of SAS/ACCESS Databases

The following LIBNAME options can be used to tune performance of the SAS/ACCESS engines. You can set these options when you use the New Library wizard to register the database libraries in the metadata repository. To access the Advanced Options dialog box, click the Advanced Options button on the Library Options window of the New Library wizard.

The Optimization Tab in the Advanced Options Dialog Box for a DB2 Library for UNIX and PC

[The Optimization Tab in the Advanced Options Dialog Box for a DB2 Library for UNIX and PC]

The tabs that are available in the Advanced Options dialog box, as well as the options on each of the tabs, vary between database management systems. The following list provides a description of the options on Optimization tab for DB2 libraries for UNIX and PC:

Block insert buffer size (INSERTBUFF=)

specifies the number of rows in a single insert operation. See Buffering Data for DB2 (UNIX and PC), ODBC, OLE DB, Oracle, SQL Server, and Sybase Tables.

Block read buffer size (READBUFF=)

specifies the number of rows of DBMS data to read into the buffer. See Buffering Data for DB2 (UNIX and PC), ODBC, OLE DB, Oracle, SQL Server, and Sybase Tables.

Pass functions to the DBMS that match those supported by SAS (SQL_ FUNCTIONS=)

when set to ALL, specifies that functions that match functions supported by SAS should be passed to the DBMS. The functions that are passed are: DATE, DATEPART, DATETIME, TIME, TIMEPART, TODAY, QRT, COMPRESS, SUBSTR, DAY, SECOND, INDEX, TRANWRD, HOUR, WEEKDAY, LENGTH, TRIMN, MINUTE, YEAR, REPEAT, MOD, MONTH, BYTE, and SOUNDEX. Use of this option can cause unexpected results, especially if used for NULL processing and date, time, and timestamp handling. Exercise care when using this option.

Pass DELETE to the DBMS (DIRECT_EXE=)

specifies that an SQL delete statement is passed directly to the DBMS for processing. Selecting this option improves performance because SAS does not have to read the entire result set and delete one row at a time.

Whether to use indexes (DBINDEX=)

specifies whether SAS uses indexes that are defined on DBMS columns to process a join. Valid values are YES or NO. For more information about indexes, see Indexing Data.

Whether to check for null keys when generating WHERE clauses (DBNULLKEYS=)

specifies whether the WHERE clause should detect NULL values in columns. Valid values are YES or NO. YES is the default for most interfaces and enables SAS to prepare the statement once and use it for any value (NULL or NOT NULL) in the column.

Multi data source optimization (MULTI_ DATASRC_OPT=)

when processing a join between two tables, specifies whether an IN clause should be created to optimize the join. Valid values are NONE and IN_CLAUSE. IN_CLAUSE specifies that an IN clause containing the values read from a smaller table will be used to retrieve the matching values in a larger table based on a key column designated in an equi-join.

When processing a join between a SAS table and a DBMS table, the SAS table should be smaller than the DBMS table for optimal performance.

Whether to create a spool file for two-pass processing (SPOOL=)

specifies whether to create a utility spool file during transactions that read data more than once. In some cases, SAS processes data in more than one pass through the same set of rows. Spooling is the process of writing rows that have been retrieved during the first pass of a data read to a spool file. In the second pass, rows can be re-read without performing I/O to the DBMS a second time. In cases where the data needs to be read more than once, spooling improves performance. Spooling also guarantees that the data remains the same between passes. Valid values are YES or NO.

Threaded DBMS access (DBSLICEPARM=)

specifies the scope of DBMS threaded reads and the number of threads. If this option is set to the default, then PROC SQL will not use threading to read, for example, data for a Web report. To force a specified number of threads for a threaded read from the DBMS server, change the default to (ALL,number-of-threads).

Note:   If PROC SQL attempts implicit pass-through, then threading will be disabled, regardless of the Threaded DBMS access setting. To disable implicit pass-through, set the Pass generated SELECT SQL to the DBMS - DBMS processing option to NO.  [cautionend]

For more information about threaded reads, see Using Threaded Reads.

Pass generated SELECT SQL to the DBMS - DBMS processing (DIRECT_SQL=)

specifies whether generated SQL is passed to the DBMS for processing. Valid values are YES or NO.

Pass generated SELECT SQL to the DBMS - exceptions to DBMS processing (DIRECT_SQL=)

if the value for the previous option is YES, then this option specifies how generated SQL is passed to the DBMS for processing. For example, NOWHERE prevents WHERE clauses from being passed to the DBMS for processing.

The Other Options tab, which is available for all database management systems, can be used to specify LIBNAME options such as COMPRESS= (see Compressing Data).

For information about each of the LIBNAME options in the Advanced Options dialog box, click the Help button. For information about all SAS/ACCESS LIBNAME options, see SAS/ACCESS for Relational Databases: Reference.

Previous Page | Next Page | Top of Page