Tune Statements and Use Indexes
Performance impact: Large
If only col1 from table t1 is needed, use
the statement:
SELECT col1 FROM t1...
instead of using:
SELECT * FROM t1...
The TimesTen Query Optimizer describes how to view the plan that TimesTen uses to run a statement.
Alternatively, you can use the ttIsql showplan command to view the
plan. View the plan for each frequently run statement in the application. If indexes are
not used to evaluate predicates, consider creating new indexes or rewriting the
statement or query so that indexes can be used. For example, indexes can only be used to
evaluate WHERE clauses when single columns appear on one side of a
comparison predicate (equalities and inequalities), or in a BETWEEN
predicate.
If this comparison predicate is evaluated often, it would therefore make sense to rewrite
WHERE c1+10 < c2+20
to
WHERE c1 < c2+10
and create an index on c1.
The presence of indexes does slow down write operations such as UPDATE, INSERT, DELETE and CREATE VIEW. If an application does few reads but many writes to a table, an index on that table may hurt overall performance rather than help it.
Occasionally, the system may create a temporary index to speed up query evaluation. If this happens frequently, it is better for the application itself to create the index. The CMD_TEMP_INDEXES column in the MONITOR table indicates how often a temporary index was created during query evaluation.
If you have implemented time-based aging for a table or cache group, create an index on the timestamp column for better performance of aging. See Time-Based Aging.