Understanding optimization goals

A central concept of Adaptive Server 15.0's query processing engine is the “optimization goal”, which provides an indication of the nature of the query being optimized. The Adaptive Server query optimizer determines how best to optimize a query based on optimization goals.

For example, a typical OLTP (online transaction processing) query and a typical DSS (decision-support system) query result in very different query plans due to the different data access patterns used by these queries. OLTP queries generally affect only one or a few rows and join only a few well-indexed tables. However, DSS queries typically affect many rows, return a few rows, and may join many tables.Because of their different access patterns, OLTP queries often run most efficiently using a classic “nested-loop join”, whereas DSS queries are more likely to run faster with a “hash join”. If you indicate that a query is for OLTP or DSS purposes, the optimizer uses that information to generate a query plan that may save time, memory, and CPU usage.

Adaptive Server 15.0 provides three optimization goals, ordered from “narrow” to “wide,” which correspond to the number of options and strategies that they allow the optimizer to consider:

If you use allrows_mix and allrows_dss, additional low-level processing algorithms are enabled for SQL operations; these algorithms are disabled if you use allrows_oltp.When you widen the optimization goal, the query optimizer might use significantly more resources (time and procedure cache) to generate a query plan. If the optimizer generates the same query plan, with only nested-loop joins, under allrows_dss and allrows_oltp, you may expect the optimization under allrows_dss to take more time and procedure cache than under allrows_oltp.

The choice of optimization goal can have a significant impact on query performance. If you know that a certain application has different workload characteristics than the rest of your system, you may want to set an appropriate session-level optimization goal for that application. Either use the QPTune utility, or manually experiment with different optimization goals, and select one that provides the best overall performance for your particular set of applications and queries. See Chapter 2, “QPTune” for more details.

You can define the optimization goal at the server-, session- or individual- query level:

NoteYou can also use a login trigger to set the session-level optimization goal.