Introduction to NonStop SQL/MP

Parallel Execution
NonStop SQL Architecture
113425 Tandem Computers Incorporated 3–15
Managing Transactions
If the SQL query is part of an update transaction, the executor invokes the
NonStop TM/MP subsystem for transaction management services. You define a
transaction in an application program by placing a BEGIN WORK statement before the
SQL statements that will execute within the transaction. At run time, the SQL executor
processes the BEGIN WORK statement, calling NonStop TM/MP to initiate the
transaction.
The SQL executor then executes the SQL statements, but the updates are not
committed to the database until the entire transaction has been completed
successfully.
When all the results are returned successfully to the application, the executor calls
NonStop TM/MP to commit the transaction. (A COMMIT WORK statement following
the SQL statements in the program indicates successful completion of the transaction.)
If any part of the transaction does not finish successfully, NonStop TM/MP rolls back
the entire transaction, leaving the database in a consistent state.
Parallel Execution In most OLTP and electronic commerce applications, many small transactions execute
concurrently. The Tandem multiprocessor architecture makes it possible for these
transactions to execute in parallel. Different SQL queries are assigned to different
processors, where copies of NonStop SQL/MP concurrently retrieve and update the
data. This type of processing, in which multiple transactions are performed in parallel,
is called inter-query parallelism.
An executing query can access any portion of the database, no matter where it is
located. Tandem's message-based operating system allows a query executing in one
processor to communicate with a data access manager in another processor in the
same system or even another node in a geographically distributed network.
NonStop SQL can also execute large queries, which scan large portions of the
database, in parallel. These queries are typical of DSS applications, in which users
might need to examine large subsets of the data to make complex decisions. To
perform these large queries efficiently, NonStop SQL/MP carries out parallel
processing within a query. Such intra-query parallelism fully exploits the resources
provided by the Tandem multiprocessor, multidisk architecture and improves
performance on large queries.
When you select the option to allow parallel execution for a query, NonStop SQL/MP
can automatically perform intra-query parallelism by dividing a large query into
smaller tasks and assigning the tasks to separate processors. When a database spans
multiple disks, these separate SQL processes can access the disks in parallel, greatly
improving the performance of a query.
Parallel query execution can improve the response time of all the basic SQL
operations, including selects, inserts, updates, deletes, joins, and aggregate functions.