You are viewing the documentation for a prerelease version.

View Latest

PREPARE

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
'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`
WHERE type="hotel" AND 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`
WHERE type="hotel" AND 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`
WHERE type="hotel" AND 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

(Introduced in Couchbase Server 6.5)

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 prepare 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.

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` and `beer-sample`.

PREPARE SELECT * FROM `travel-sample`
WHERE city = (SELECT RAW city FROM `beer-sample`)

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

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

Example

Query
PREPARE SELECT * FROM `travel-sample`
WHERE type = "route"
AND airline = "FL";
Result
{
  "encoded_plan": "H4sIAAAAAAAA/wEAAP//AAAAAAAAAAA=",
  "featureControls": 12,
  "indexApiVersion": 3,
  "name": "[127.0.0.1:8091]5944e03f-aa9a-5f02-8fc9-f54070322758",
  "namespace": "default",
  "operator": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Authorize",
        "privileges": {
          "List": [
            {
              "Priv": 7,
              "Target": "default:travel-sample"
            }
          ]
        },
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IndexScan3",
              "index": "def_type",
              "index_id": "ff413bfa5f5869f4",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "travel-sample",
              "namespace": "default",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"route\"",
                      "inclusion": 3,
                      "low": "\"route\""
                    }
                  ]
                }
              ],
              "using": "gsi"
            },
            {
              "#operator": "Fetch",
              "keyspace": "travel-sample",
              "namespace": "default"
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Filter",
                    "condition": "(((`travel-sample`.`type`) = \"route\") and ((`travel-sample`.`airline`) = \"FL\"))"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "self",
                        "star": true
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "#operator": "Stream"
      }
    ]
  },
  "signature": {
    "*": "*"
  },
  "text": "PREPARE SELECT * FROM `travel-sample`\nWHERE type = \"route\"\nAND airline = \"FL\";"
}
  • For information on executing the prepared statement, refer to EXECUTE.