Some tactical queries require all-AMPs steps. All-AMPs queries are likely to have a more relaxed response time expectation than single-AMP queries even when they are capable, at times of low concurrency, of subsecond response.
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 follow:
- Review the queries for unnecessary database access. If found, eliminate those accesses from the query.
- Tune row-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 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 query.