PREPARE

reference

The PREPARE statement prepares a query for repeated execution.

Purpose

Building plans for SQL++ requests may be expensive, in particular where a cluster has many indexes. Sometimes planning may take more time than actually executing a request.

If you know that a statement text will be executed repeatedly, you can request the SQL++ service to prepare the execution plan beforehand, and then request to execute the prepared plan as many times as needed, thereby avoiding the cost of repeated planning.

Prerequisites

RBAC Privileges

The user executing the PREPARE statement must have the RBAC privileges of the statement being prepared. For more details about user roles, refer to Authorization.

RBAC Examples

For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

To execute the following statement, user must have the Query Select privilege on both keyspaces airport and landmark.

PREPARE SELECT * FROM airport
WHERE city = (SELECT RAW city FROM landmark)

To execute the following statement, user must have the Query Update and Query Select privileges on hotel.

PREPARE UPDATE hotel
SET city = "San Francisco" WHERE lower(city) = "sanfrancisco"
RETURNING *

Query Context

A prepared statement is created and stored relative to the current query context. You can create multiple prepared statements with the same name, each stored relative to a different query context. This enables you to run multiple instances of the same application against different datasets.

To execute a prepared statement, the query context must be the same as it was when the prepared statement was created; otherwise the prepared statement will not be found.

You must therefore set the required query context, or unset the query context if necessary, before creating the prepared statement. If you do not set the query context, it defaults to the empty string.

For further information, refer to Query Context.

Syntax

prepare ::= 'PREPARE' 'FORCE'? ( name ( 'FROM' | 'AS' ) )? statement

statement: The full text of the SQL++ statement to prepare. The SQL++ statement may contain parameters. For more details, refer to Parameters below.

FORCE

[Optional] The FORCE keyword forces the query engine to create the prepared statement again, even if a matching prepared statement already exists in the cache. For more details, refer to Statement Cache below.

The FORCE keyword does not enable you to assign a new prepared statement to an existing name.

FROM / AS Clause

[Optional] The FROM or AS clause enables you to specify a name for the prepared statement.

name: A local name for the prepared statement. If you do not specify a local name for the prepared statement, the query engine generates a UUID from the statement text. For more details, refer to Result below.

Parameters

A prepared statement may contain parameters. These are replaced by a supplied value when the statement is executed. Parameters may be named parameters or positional parameters.

Named parameters are specified by name when the prepared statement is executed. To refer to a named parameter in a statement, use $ followed by the name of the parameter, e.g. $city. See Example 2.

Positional parameters are specified by the position of each supplied parameter when the statement is executed. To refer to a positional parameter in a statement, use $ followed by the position of the supplied parameter. So $1 refers to the first supplied parameter, $2 refers to the second supplied parameter, etc. See Example 3.

You may also use ? to refer to a positional parameter in a statement. In this case, the order of parameters in the statement must exactly match the order of parameters when the statement is executed. So the first ? refers to the first supplied parameter, the second ? refers to the second supplied parameter, etc. See Example 4.

Result

A JSON object is returned that contains the following properties:

name

The full name of the prepared statement. This has the format [host:port]local-name-or-UUID, and consists of:

The host and port of the node where the prepared statement was created, in square brackets, followed by
The local name that you specified for the prepared statement, or a UUID that was generated from the statement text.

The host and port can be used when executing to retrieve the prepared statement from the node where it was created.

operator

The execution plan of the statement being prepared.

signature

The signature of the statement being prepared.

text

The full PREPARE statement text.

encoded_plan

The full prepared statement in encoded format. This is included for backward compatibility. In versions of Couchbase Server prior to Couchbase Server 6.5, you can use the encoded plan in a request to execute a prepared statement.

Statement Cache

Prepared statements are stored in the prepared statement cache until you restart the Couchbase Server.

In Couchbase Server 6.5 and later, the query engine uses the prepared statement cache to speed up the creation of prepared statements.

When you create a prepared statement with a local name:

The query engine checks whether a prepared statement with that name already exists.
- If it does not, the prepared statement is created.
- If it does, the query engine checks whether the text of your SQL++ statement matches the SQL++ statement associated with the existing prepared statement.
  - If it does not match, a duplicate name error is generated.
  - If it matches, the existing prepared statement is returned. However, if the FORCE keyword is present, the prepared statement is created again.

When you create an anonymous prepared statement, i.e. a prepared statement without a local name:

The query engine generates a UUID from the statement text.
The query engine then searches the prepared cache to see if the UUID is already listed.
- If not found, the statement is created and added to the prepared cache.
- If found, the existing prepared statement is returned. However, if the FORCE keyword is present, the prepared statement is created again.

When you create an anonymous prepared statement, if there is a named prepared statement in the cache with identical statement text, the named prepared statement is not returned. The anonymous prepared statement is added to the cache in addition to the named prepared statement.

Auto-Prepare

When the auto-prepare feature is active, a prepared statement is created every time you submit a SQL++ request, whether you use the PREPARE statement or not.

The process is similar to creating a prepared statement without a local name:

The query engine generates a UUID from the statement text.
The query engine then searches the prepared cache to see if the UUID is already listed.
- If found, the existing prepared statement is returned.
- If not found, the statement is created and added to the prepared cache.

The auto-prepare feature is inactive by default. You can turn the auto-prepare feature on or off using the auto-prepare service-level query setting. For more details, refer to Query Settings.

Auto-prepare is disabled for SQL++ requests which contain parameters, if they do not use the PREPARE statement.

Auto-Reprepare

When the auto-reprepare feature is active, a prepared statement automatically updates (reprepares) its plan whenever the GSI metadata version changes. This happens when you create or drop an index, allowing prepared statements to use newer, more efficient indexes as they become available.

To enable this feature, you must set bit 23 (0×800000 or 8388608) of the n1ql-feat-ctrl setting. For information about how to set this value, see the table in the Manage Sequential Scans section.

By default, a prepared statement is only reprepared if an index in its current plan becomes unavailable. With auto-reprepare, prepared statements can adapt to new indexes as well. For example:

When the feature is inactive: If no indexes exist when you prepare a statement, then the prepared plan uses a sequential scan. If you create a primary index or a secondary index later, the statement still continues to use the sequential scan and does not automatically benefit from the new indexes.
When the feature is active: If you create a primary index after preparing a statement, the next time the statement executes, the Query Service generates a new plan using the primary index. Similarly, if a more optimal secondary index becomes available, the service flags the statement again and generates a new plan on the next execution using the new index.

Although this feature improves performance, it has a potential drawback. Any change to indexes, even those unrelated to a specific statement, can trigger an update. For example, creating an unrelated secondary index or dropping an unused primary index can cause a statement to be reprepared. While the statement may select the existing plan again, this can lead to additional load. If index changes are infrequent, the impact is minimal.

To manage this effectively, you can enable or disable this feature as needed. For example, you can enable the feature before creating new indexes and then disable it after all statements are reprepared.

Manual Reprepare

In addition to the automatic mode, you can also manually reprepare a statement. To do this, update system:prepareds and unset the planPreparedTime field for the statement.

For example, to reprepare a prepared statement named NumParam on a node with the IP address 127.0.0.1 and port 8091, use the following query:

UPDATE system:prepareds USE KEYS ["[127.0.0.1:8091]NumParam"] UNSET planPreparedTime;

You can repeat this operation after creating each relevant index to refresh the prepared statement’s plan.

Auto-Execute

When the auto-execute feature is active, a prepared statement is executed automatically as soon as it is created. This saves you from having to make two separate SQL++ requests in cases where you want to prepare a statement and execute it immediately.

When this feature is active, a SQL++ request to prepare a statement returns the result of the execution step. It does not return the full result of the preparation step, such as the execution plan. However, the output of the SQL++ request does include a prepared field, which contains the full name of the prepared statement. You can use this when you need to execute the prepared statement again.

The auto-execute feature is inactive by default. You can turn the auto-execute feature on or off using the auto_execute request-level query setting. For more details, refer to Query Settings.

The auto-execute feature only works for SQL++ requests which actually contain the PREPARE statement. Prepared statements created by the auto-prepare feature are not executed by the auto-execute feature.

Statement Propagation

When prepared, new statements are distributed to all query nodes.

In Couchbase Server 6.5 and later, when a query node is started or restarted, the prepared statement cache is primed from another node.

If it is not possible to prime the statement cache from another node, you must prepare the statements again before you can execute them.

Examples

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Example 1. Prepared statement

Query

PREPARE SELECT * FROM route
WHERE airline = "FL";

Result

[
  {
    "encoded_plan": "H4sIAAAAAAAA/wEAAP//AAAAAAAAAAA=",
    "featureControls": 12,
    "indexApiVersion": 4,
    "indexScanKeyspaces": {
      "default:travel-sample.inventory.route": false
    },
    "name": "[127.0.0.1:8091]26898aa0-04b2-518c-aa11-2fd13cd377b1",
    "namespace": "default",
    "operator": {
      "#operator": "Authorize",
      "privileges": {
        "List": [
          {
            "Priv": 7,
            "Props": 0,
            "Target": "default:travel-sample.inventory.route"
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "PrimaryScan3",
                "bucket": "travel-sample",
                "index": "def_inventory_route_primary",
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "route",
                "namespace": "default",
                "scope": "inventory",
                "using": "gsi"
              },
              {
                "#operator": "Fetch",
                "bucket": "travel-sample",
                "keyspace": "route",
                "namespace": "default",
                "scope": "inventory"
              },
              {
                "#operator": "Parallel",
                "~child": {
                  "#operator": "Sequence",
                  "~children": [
                    {
                      "#operator": "Filter",
                      "condition": "((`route`.`airline`) = \"FL\")"
                    },
                    {
                      "#operator": "InitialProject",
                      "result_terms": [
                        {
                          "expr": "self",
                          "star": true
                        }
                      ]
                    }
                  ]
                }
              }
            ]
          },
          {
            "#operator": "Stream"
          }
        ]
      }
    },
    "queryContext": "",
    "signature": {
      "*": "*"
    },
    "text": "PREPARE SELECT * FROM route\nWHERE airline = \"FL\";",
    "useCBO": true
  }
]

Example 2. Prepared statement with named parameters

PREPARE NameParam AS
SELECT * FROM hotel
WHERE city=$city AND country=$country;

Example 3. Prepared statement with numbered parameters

PREPARE NumParam AS
SELECT * FROM hotel
WHERE city=$1 AND country=$2;

Example 4. Prepared statement with positional parameters

PREPARE NumParam AS
SELECT * FROM hotel
WHERE city=? AND country=?;

For information on executing the prepared statement, refer to EXECUTE.
For information on using prepared statements with the cbq command line shell, refer to cbq: The Command Line Shell for SQL++.
For information on using prepared statements with the Query REST API (/query/service endpoint), refer to Query Service REST API.
For information on using prepared statements with an SDK, refer to Prepared Statements for Query Optimization and Parameterized Queries.

PREPARE

Purpose

Prerequisites

RBAC Privileges

Query Context

Syntax

FORCE

FROM / AS Clause

Parameters

Result

Statement Cache

Auto-Prepare

Auto-Reprepare

Manual Reprepare

Auto-Execute

Statement Propagation

Examples

Related