Optimizing Process Flows - SAS Data Integration Studio 3.3 User's Guide - page 182

Building Efficient Process Flows
Introduction to Building Efficient Process Flows
Building efficient processes to extract data from operational systems, transform it,
and load it into the star schema data model is critical to the success of your process
flows. Efficiency takes on greater importance as data volumes and complexity increase.
This section describes some simple techniques that can be applied to your processes to
improve their performance.
Choosing Between Views or Physical Tables
In general, each step in a process flow creates an output table that becomes the input
for the next step in the flow. Consider what format would be best for transferring data
between steps in the flow. There are two choices:
￿ write the output for a step to disk (in the form of SAS data files or RDBMS tables)
￿ create views that process input and pass the output directly to the next step, with
the intent of bypassing some writes to disk
SAS supports two kinds of views, SQL views and DATA Step views, and the two types
of views can behave differently. Switching from views to physical tables or tables to
views sometimes makes little difference in a process flow. At other times, improvements
can be significant. The following tips are useful:
￿ If the data that is defined by a view is only referenced once in a process flow, then
a view is usually appropriate.
￿ If the data that is defined by a view is referenced multiple times in a process flow,
then putting the data into a physical table will likely improve overall performance.
As a view, SAS must execute the underlying code repeatedly, each time the view is
accessed.
￿ If the view is referenced once in an process flow, but the reference is a
resource-intensive procedure that performs multiple passes of the input, then
consider using a physical table.
￿ If the view is SQL and is referenced once, but the reference is another SQL view,
then consider using a physical table. SAS SQL optimization can be less effective
when views are nested. This is especially true if the steps involve joins or RDBMS
sources.
￿ If the view is SQL and involves a multi-way join, it is subject to performance
limitations and disk space considerations.
Assess the overall impact to your process flow if you make changes based on these tips.
In some circumstances, you might find that you have to sacrifice performance in order
to conserve disk space.
Some of the standard transformations provided with SAS Data Integration Studio
have a Create View option on their Options tabs, or a check box that serves the same
purpose. Some of the transformations that enable you to specify a view format or a
physical table format for their temporary output tables include the following:
￿ Append
￿ Data Validation
￿ Extract