PREPARE

reference

The PREPARE statement prepares a query for repeated execution.

Purpose

Building plans for N1QL 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 N1QL 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.

Syntax

prepare ::= PREPARE [ FORCE ] [ name ( FROM | AS ) ] statement

statement: The full text of the N1QL statement to prepare. The N1QL 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.

Usage

You can prepare a statement in three ways:

Using the PREPARE statement in the Query Workbench.
Using the PREPARE statement in the the cbq command line shell.
Using the Query REST API (/query/service endpoint).

For information on how to use prepared statements with various SDKs, refer to Querying with N1QL and N1QL from the SDK.

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.

PREPARE NameParam AS
SELECT * FROM `travel-sample`.inventory.hotel
WHERE city=$city AND country=$country;

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.

PREPARE NumParam AS
SELECT * FROM `travel-sample`.inventory.hotel
WHERE city=$1 AND country=$2;

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.

PREPARE NumParam AS
SELECT * FROM `travel-sample`.inventory.hotel
WHERE city=? AND country=?;

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 in Couchbase Server 6.5 for for compatibility with previous versions. In previous versions of Couchbase Server, 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 N1QL statement matches the N1QL 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 N1QL 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 N1QL requests which contain parameters, if they do not use the PREPARE statement.

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 N1QL requests in cases where you want to prepare a statement and execute it immediately.

When this feature is active, a N1QL 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 N1QL 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 N1QL 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.

Authorization

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.

For example,

To execute the following statement, user must have the Query Select privilege on both keyspaces `travel-sample`.inventory.airport and `travel-sample`.inventory.landmark.

PREPARE SELECT * FROM `travel-sample`.inventory.airport
WHERE city = (SELECT RAW city FROM `travel-sample`.inventory.landmark)

To execute the following statement, user must have the Query Update and Query Select privileges on `travel-sample`.inventory.hotel.

PREPARE UPDATE `travel-sample`.inventory.hotel
SET city = "San Francisco" WHERE lower(city) = "sanfrancisco"
RETURNING *

Example

Query

PREPARE SELECT * FROM `travel-sample`.inventory.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 `travel-sample`.inventory.route\nWHERE airline = \"FL\";",
    "useCBO": true
  }
]

For information on executing the prepared statement, refer to EXECUTE.

PREPARE

Purpose

Syntax

FORCE

FROM / AS Clause

Usage

Parameters

Result

Statement Cache

Auto-Prepare

Auto-Execute

Statement Propagation

Authorization

Example

Related