SPD Server Performance Enhancements

You can use SQL pass-through to directly submit to the SPD Server SQL statements that use SPD Server tables. The SPD Server SQL Planner has several optimizations that you can use to create SQL queries that take advantage of SMP and table indexes, resulting in improved SQL query performance. See SPD Server SQL Features for more information about SPD Server pass-through SQL enhancements.

You can use implicit or explicit sorts with SPD Server. For example, PROC SORT in Base SAS is an explicit sort. You can use PROC SORT with SPD Server as well.

An implicit sort is unique to SPD Server. Each time you submit a SAS statement with a BY clause, SPD Server sorts your data, unless the table is already sorted or indexed by the BY column. The automatic sort is convenient. Accessing and Creating SAS Scalable Performance Data (SPD) Server Tables contains tips on how and when to use each sort type.

SPD Server uses heapsort as its default sort with a few changes. In SPD Server, heapsort compares the available memory on the server to the memory that is required to load and process the index key data in memory. If the memory is not constrained, SPD Server performs the heapsort in RAM memory.

SPD Server indexes are designed to support parallelism. Experienced RDBMS users are accustomed to a processing lag that occurs when databases must read or process enormous tables.When SPD Server performs table queries, the SPD Server index architecture enables the software to analyze different table sections or segments in parallel. By processing large table segments in parallel, SPD Server delivers much faster data throughput. The faster throughput might be difficult to perceive on small tables, but when SPD Server performs scans on very large tables, the processing performance is significantly faster than database systems that support only serial indexed table scans.

SPD Server separates the table append operation into steps that can be performed in parallel. The level of parallelism depends on the number of indexes in the table. The more indexes you have, the greater the potential of exploiting parallelism during the append.

Tip: You can save time by creating an empty table in SPD Server, define your indexes on it, and then appending the data, as opposed to loading the table, and then creating the indexes. It is faster to create indexes on an empty table.