8.4 Working with Prepared Statements

X DevAPI improves performance for each CRUD statement that is executed repeatedly by using a server-side prepared statement for its second and subsequent executions. This happens internally—applications do not need to do anything extra to utilize the feature, as long as the same operation object is reused.

When a statement is executed for a second time with changes only in data values or in values that refine the execution results (for example, different offset() or limit() values), the server prepares the statement for subsequent executions, so that there is no need to reparse the statement when it is being run again. New values for re-executions of the prepared statement are provided with parameter binding. When the statement is modified by chaining to it a method that refines the result (for example, sort(), limit(), or offset()), the statement is reprepared. The following pseudocode and the comments on them demonstrate the feature:

var f = coll.find("field = :field");
f.bind("field", 1).execute(); // Normal execution
f.bind("field", 2).execute(); // Same statement executed with a different parameter value triggers statement preparation
f.bind("field", 3).execute(); // Prepared statement executed with a new value
f.bind("field", 3).limit(10).execute(); // Statement reprepared as it is modified with limit()
f.bind("field", 4).limit(20).execute(); // Reprepared statement executed with new parameters

Notice that to take advantage of the feature, the same operation object must be reused in the repetitions of the statement. Look at this example

for (i=0; i<100; ++i) {
    coll.find("field = :field").bind("field", i).execute();
}

This loop cannot take advantage of the prepared statement feature, because the operation object of coll.find() is recreated at each iteration of the for loop. Now, look at this example:

var f = coll.find("field = :field");
 
for (i=0; i<100; ++i) {
    f.bind("field", i).execute();
}

The repeated statement is prepared once and then reused, as the same operation of coll.find() is re-executed for each iteration of the for loop.

Prepared statements are part of a Session. When a Client resets the Session (by using, for example, Mysqlx.Session.Reset), the prepared statements are dropped.