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, B035-1142 for more information) or set the default session read lock to READ UNCOMMITTED using the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL statement (see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144).
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.