Output 5.1 Resetting PROC SQL Options with the RESET Statement
Improving Query Performance
Overview of Improving Query Performance
There are several ways to improve query performance, including the following:
using indexes and composite indexes
using the keyword ALL in set operations when you know that there are no duplicate
rows, or when it does not matter if you have duplicate rows in the result table
omitting the ORDER BY clause when you create tables and views
using in-line views instead of temporary tables (or vice versa)
using joins instead of subqueries
using WHERE expressions to limit the size of result tables that are created with joins
using either PROC SQL options, SAS system options, or both to replace a PUT
function in a query with a logically equivalent expression
replacing references to the DATE, TIME, DATETIME, and TODAY functions in a
query with their equivalent constant values before the query executes
disabling the remerging of data when summary functions are used in a query
Using Indexes to Improve Performance
Indexes are created with the CREATE INDEX statement in PROC SQL or with the
MODIFY and INDEX CREATE statements in the DATASETS procedure. Indexes are
stored in specialized members of a SAS library and have a SAS member type of INDEX.
The values that are stored in an index are automatically updated if you make a change to
the underlying data.
Indexes can improve the performance of certain classes of retrievals. For example, if an
indexed column is compared to a constant value in a WHERE expression, then the index
will likely improve the query's performance. Indexing the column that is specified in a
correlated reference to an outer table also improves a subquery's (and hence, query's)
performance. Composite indexes can improve the performance of queries that compare
142 Chapter 5 Programming with the SQL Procedure

Get SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.