Enabling Pass-Through Processing

Problem

You want to decide whether to enable pass-through processing, which sends DBMS-specific statements to a database management system and retrieves the DBMS data directly. In some situations, pass-through processing can improve the performance of the SQL Join transformation in the context of a SAS Data Integration Studio job. Pass-through processing is enabled with options that are found on the SQL Properties pane and the properties panes for the tables found in SAS queries.

Solution

You can use the Pass Through property on the SQL Join Properties pane to determine whether explicit pass-through processing is used. When the Pass Through property is set to Yes, you can send DBMS-specific statements to a database management system and retrieve DBMS data directly, which sometimes is faster than processing the SQL query on the SAS system. When Pass Through is set to No, explicit pass-through processing is not used.

You can use the following types of pass-through processing:

Tasks

Explicit Pass-Through Processing

Explicit pass-through is not always feasible. The query has to be able to work as is on the database. Therefore, if the query contains anything specific to SAS beyond the outermost select columns portion, the database generates errors. For example, using any of the following in a WHERE clause expression or in a subquery on the WHERE or FROM clauses causes the code to fail on the database if pass through is set to Yes:

SAS formats
SAS functions
DATE or DATETIME literals or actual numeric values
date arithmetic (usually does not work)
INTO: macro variable
data set options

The SQL Properties pane also contains the Target Table is Pass Through property, which determines whether explicit pass-through is active for the target table. This property enables the target to have the select rows inserted into the target within the explicit operation. This property is valid only when all the tables in the query, including the target, are on the same database server. The Target Table is Pass Through property has a corresponding property, named Target Table Pass Through Action. The Truncate option in this property is useful for DBMS systems that does not allow the target to be deleted or created. In this case, the only option is removing all of the rows. If Truncate is selected, all of the rows in the table are deleted. If the table does not exist, it is created.

Implicit Pass-Through Processing

Even if Pass Through is set to No, PROC SQL still tries to pass the query or part of the query down to the database with implicit pass-through. This attempt to optimize performance is made without the user having to request it. SQL implicit pass-through is a silent optimization that is done in PROC SQL. Implicit pass-through interprets SAS SQL statements, and, whenever possible, rewrites the SAS SQL into database SQL.

There is no guarantee that the SQL is passed to the database. However, PROC SQL tries to generate SQL that passes to the database. If the optimization succeeds in passing a query (or parts of a query) directly to a database, the SQL query executes on the database and only the results of the query are returned to SAS. This approach can greatly improve the performance of the PROC SQL code. If the query cannot be passed to the database, records are read and passed back to SAS, one at a time. Implicit pass-through is disabled by the following query constructs:

Heterogeneous queries: Implicit pass-through is not attempted for queries that involve different engines or on queries that involve a single engine with multiple librefs that cannot share a single connection because they have different connection properties (such as a different database= value). You can use the Pass Through property to run these queries with explicit pass-through processing. You can also use the Upload Library Before SQL, Pre-Upload Action, and Use Bulkload for Upload properties in the table properties panes to improve the situation.

Note: The Upload Library Before SQL property can be used to create a homogeneous join, which then can enable an explicit pass-through operation. This property allows you to select another library on the same database server as other tables in the SQL query. The best choice for a library is a temporary space on that database server. The operations on that temporary table can also be modified to choose between deleting all rows or deleting the entire table. Bulk-load is also an option for the upload operation with the Use Bulkload for Uploading property. It is generally a good practice to upload the smaller of the tables in the SQL query because this operation can be expensive.
Queries that incorporate explicit pass-through statements: If explicit pass-through statements are used, the statements are passed directly to the database as they are. Therefore, there is no need to try to prepare or translate the SQL with implicit pass-through to make it compatible to the database. It is already assumed to be compatible.
Queries that use SAS data set options: SAS data set options cannot be honored in a pass-through context.
Queries that use an INTO: clause: The memory that is associated with the host variable is not available to the DBMS that processes the query. The INTO: clause is not supported in the SQL Join transformation.
Queries that contain the SAS OUTER UNION operator: This operator is a non-ANSI SAS SQL extension.
Specification of a SAS Language function that is not mapped to a DBMS equivalent by the engine. These functions vary by database.
Specification of ANSIMISS or NOMISS in the join syntax.