Stop Indexing Everything: Balancing Query Performance vs. Disk Space in SAS

Difficulty Level

Intermediate

Published on : 16/11/2022

Expert Advice

Michael
Responsable de l'infrastructure Viya.

SAS enforces a strict naming convention for Simple Indexes (indexes on a single column) that differs from many other databases. As shown in your first code example (create index area on newcountries(area)), a simple index must share the exact same name as the variable it indexes. If you try to give it a custom name (e.g., create index my_idx on newcountries(area)), SAS will reject it. However, Composite Indexes (on multiple columns) must have a unique name that is distinct from any column in the table.

Don't index everything—index selectively for targeted performance.

Indexes should be created for columns with many unique values or those frequently used in joins. It is recommended to limit the number of indexes to reduce disk space and update costs. Indexes are particularly effective for queries that retrieve a relatively small number of rows (less than 15%). Indexing small tables or columns with few distinct values generally does not lead to performance gains. The name of a simple index must match the name of the column it indexes, while a composite index cannot have the same name as an existing column.

Data Analysis

Type : CREATION_INTERNE

Examples use generated data (CREATE TABLE ... LIKE) from an implicitly available table (COUNTRIES, often from SASHELP) or created tables.

1 Code Block

PROC SQL Data

Explanation :
This example creates a new table named `newcountries` using the structure of the `countries` table. Then, it creates a simple index named `area` on the `area` column of the `newcountries` table. The index name here matches the column name.

Copied!

1	PROC SQL;
2	create TABLE newcountries
3	like countries;
4	create index area
5	on newcountries(area);
6	QUIT;

2 Code Block

PROC SQL

Explanation :
This example creates a composite index named `places` on the `name` and `continent` columns of the `newcountries` table. A composite index is used to optimize queries involving multiple columns simultaneously.

Copied!

1	PROC SQL;
2	create index places
3	on newcountries(name, continent);
4	QUIT;

3 Code Block

PROC SQL

Explanation :
This example illustrates the creation of a unique composite index. Adding the `UNIQUE` keyword ensures that each combination of values in the `name` and `continent` columns is unique. Any attempt to insert or update a row that would violate this uniqueness would be rejected by SAS.

Copied!

1	PROC SQL;
2	create TABLE newcountries
3	like countries;
4	create unique index places
5	on newcountries(name, continent);
6	QUIT;

4 Code Block

PROC SQL

Explanation :
This example shows how to drop an existing index from a table. The `DROP INDEX` command followed by the index name and the target table is used to remove the `places` index from the `newcountries` table.

Copied!

1
2	PROC SQL;
3	drop index places from newcountries;
4	QUIT;
5

Pro Tip

Use the UNIQUE keyword when creating an index (as shown in the third example) to enforce data integrity. This prevents duplicate values from ever being inserted into the table, acting as a constraint.

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Difficulty Level

Published on : 16/11/2022

Expert Advice

Don't index everything—index selectively for targeted performance.

Data Analysis

1 Code Block

2 Code Block

3 Code Block

4 Code Block

Pro Tip

Related Documentation