Introduction to NonStop SQL/MP

Summary
NonStop SQL Architecture
113425 Tandem Computers Incorporated 3–23
VSBB can be as much as three times more efficient than RSBB. VSBB is especially
important when data is geographically distributed across long-distance
communication lines.
The optimizer can also choose VSBB for insert and update operations. VSBB
significantly improves the performance of these operations as well.
Cache Optimizations for
Sequential Access
If the optimizer has chosen sequential access for a query, the data access manager can
read blocks of data into cache (memory) using a minimal number of I/O operations.
The data access manager performs bulk I/O operations, transferring up to 56 kilobytes
of data per I/O.
In addition, the data access manager can asynchronously “prefetch” data into cache—
that is, transfer a new block of data into cache immediately after completing the
previous transfer. Because the data access manager can anticipate the next (sequential)
block of rows to be requested, it can perform the I/O operation while the application
process is still processing the previous block. When the application process requests
the next block, it is already in cache. Similarly, when rows are updated sequentially,
the data access manager can minimize I/O operations by asynchronously writing
blocks to disk and by using large bulk transfers of data.
Thus, cache optimizations considerably enhance the performance of both scanning and
sequential updating of tables or key ranges.
Summary Users can configure the physical components of a NonStop SQL/MP database—
indexes, table organizations, table partitions—to maximize the performance of data
access operations. When an SQL query is compiled, the SQL optimizer automatically
determines the best execution plan for the query. At run time, the SQL executor uses
low-level system components such as the data access manager to retrieve and update
data, which further improves query performance. NonStop SQL/MP also provides
parallel query execution, support for mixed workloads, virtual sequential block
buffering, and other architectural features designed for high performance and high
availability.