IBM PureData System for Analytics, Version 7.1

Short query bias (SQB)

Within each resource group, you can reserve scheduling and memory resources for short queries. When the standard scheduler queues become full, short queries can take advantage of additional resources reserved especially for them. This enables the system to run short queries even while it is busy running long queries.

The cost of a query is the number of seconds required to run it. The optimizer uses internal mechanisms such as prep snippets to estimate the cost of each query before it is run. A query is regarded as being a short query if its cost estimate is less than or equal to the threshold specified by the host.schedSQBNominalSecs setting. The default setting is two seconds.

Typical short queries are "pick list" queries, dimensional data lookups, and other quick data lookups. They are often submitted by a business intelligence application when populating selection lists, or are entered on a SQL command line by a user who then waits for the results. Typical long queries are complex business intelligence queries that can return gigabytes or terabytes of results, or queries that perform complex joins, comparisons, or user-defined analysis. They typically take many seconds, minutes, or even hours to run. A long query can be entered on a command line, but is more commonly issued by a business intelligence application that creates scheduled reports for deep-dives into databases.

When the Short Query Bias (SQB) function is enabled, the system reserves scheduling and memory resources for short queries. When SQB is disabled, a user who runs a short query while the system is busy running long queries might experience a significant delay.

SQB is enabled by default. Changing configuration settings describes how to disable or re-enable SQB and how to modify the configuration settings that control SQB, if necessary.

When SQB is enabled and the optimizer determines that a particular query is short, it sets the SQB flag of each of the plans that is associated with that query to true. You can use scheduler rules to override the setting of an SQB flag based on any combination of the following criteria:

Which user submitted the corresponding query

You can use scheduler rules to override a plan's SQB flag based on the submitting user. For example, for each plan for a query submitted by the user bob, the following scheduler rule sets the SQB flag to false, regardless of the plan's cost estimate or the threshold set by host.schedSQBNominalSecs:

IF USER IS bob THEN SET NOT SHORT

The contents of the client information fields

An administrator or application program can set the following client information fields in a query:

User ID: The user ID under which the client is running.
Application name: The name of the client.
Workstation name: The host name of the workstation on which the client runs.
Accounting string: The value of the accounting string from the client information that is specified for the session.

You can use scheduler rules to set SQB flags to true or false based on the contents of these fields (see Client information). For example, the following scheduler rule sets the SQB flag to false for all plans of jobs submitted by the application named Cognos:

IF CLIENT_APPLICATION_NAME IS Cognos THEN SET NOT SHORT

Cost estimates

You can use scheduler rules to set SQB flags to true or false based on the calculated cost estimate, regardless of the plan's cost estimate or the threshold set by host.schedSQBNominalSecs. For example, for each plan for a query submitted by the user sam, the following scheduler rule effectively changes the short query threshold to 60 seconds:

IF USER IS sam ESTIMATE < 60 THEN SET SHORT

The database that is to be accessed

You can use scheduler rules to set SQB flags to true or false based on which databases the plans access. For example, the following scheduler rule effectively changes the short query threshold to 40 seconds for all plans that access the database dbx1 or dbx3:

IF DATABASE IN (dbx1,dbx3) ESTIMATE < 40 THEN SET SHORT

The table that is to be accessed

You can use scheduler rules to set SQB flags to true or false based on which tables the plans access. For example, the following scheduler rules sets the SQB flag to true for all plans that access the table tab1:

IF TABLE IS tab1 THEN SET SHORT

Custom tags

You can add any number of tags to sessions (see Tags). All the plans that are within the scope of that session receive the same tag. You can also create scheduler rules that add tags directly to all plans that meet the conditions specified by the rule. You can then use scheduler rules to set SQB flags to true or false based on these tags. For example, the following scheduler rule sets the SQB flag of a plan to false based on whether one or more of the specified tags have been set for the plan:

IF TAG IN (eod,eom,eoy) THEN SET NOT SHORT

Figure 1 illustrates the queues and settings used for SQB. In this example:

The GRA scheduler reserves 10 slots for short queries.
The snippet scheduler reserves 6 slots for short queries.
The SPU reserves 50 MB for short query execution.
The Netezza host reserves 64 MB for short query execution.

Figure 1. SQB queuing and priority

A: Netezza host
B: GRA scheduler
C: Snippet scheduler
D: Snippet processing unit (SPU)