Coding Suggestions for All‑AMP Tactical Queries
All-AMP tactical queries behave differently than few-AMP queries as the level of concurrency
increases. Some suggestions for coding tactical queries that involve all-AMPs operations
Review the queries for unnecessary database access. If found, eliminate those accesses
from the query.
Tune partitioned tables so queries against them can avoid unnecessary probing.
Rely on NUSI access, where possible, to avoid scanning a large table.
Define a hash or join index that partitions the base table vertically where it makes
sense to do so because scanning a join index that contains only a subset of the columns
from the base table is faster than scanning the base table.
Look for possibilities to break complex tactical queries into several smaller statements
and encapsulating them within a stored procedure, particularly when all‑AMP operations
can be replaced by multiple single‑AMP operations.
Specify explicit ACCESS locks wherever possible (see SQL Request and Transaction Processing for further information) or set the default session read lock to READ UNCOMMITTED
using the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL statement (see
SQL Data Definition Language).
ACCESS locks are more important for all‑AMP queries than for single‑AMP queries because
an all‑AMP query can require access to more data over a longer period than a single‑AMP