SQL Engine - Query Optimizer Goal

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > RDBMS - Sql Processing (Engine)

1 - About

The query optimizer determines the most efficient way to execute a SQL statement after considering many factors including the Optimizer Goal.

Advertising

3 - Choosing a goal

Optimizer Goal Description
best throughput

best response time
The optimizer chooses the least amount of resources necessary to process all rows

the first row
accessed by a SQL statement

By default, the goal of the query optimizer is the best throughput.

Choose a goal for the query optimizer based on the needs of your application:

  • For system where the last result is most important, optimize for best throughput.

Example application performed in batch, such as data access tool applications. Response time is less important, because the user does not examine the results of individual statements while the application is running.

  • For OLTP system where the user is waiting to see the first row such such as ERP, optimize for best response time.

Usually, response time is important in interactive applications, because the interactive user is waiting to see the first row or first few rows accessed by the statement.