About Prepared statements, server-side compiled query cache, or how to efficiently cache queries in YDB

There are various ways to reduce the cost of SQL query execution in modern DBMS. The most common approaches are using prepared statements and query caching. Both methods are available in YDB.

Query caching allows to compile a query once (parse it, build an optimal query plan, and JIT-compile it into native code) and then repeatedly execute it with different parameter values. This makes it possible to reduce the total query execution time by the amount of query compilation time. Also, query caching significantly reduces the amount of compute resources required for a given workload due to less frequent re-compilation of similar queries as they are happening only on the first query and on cache eviction. Below we explain why Prepare is needed in the most general cases, what difficulties arise with it in the case of a distributed DBMS and how to cache queries without Prepare.

Statements with variables

In algebra lessons at school, we were taught how to find an algorithm to solve a problem using variables. Having that, you can use different input values to calculate different results. An algebraic solution enabled us to stop wasting time rethinking the solution of the same problem each time we have a new use case for it.

To execute an SQL statement, a database server needs first to compile it producing a query execution plan — a series of internal function calls required to fulfil the instruction. It takes time and compute resources, like any compilation, and sometimes requires accessing additional data from the database like table statistics to understand which index is better to use.

Let’s take a look at this SQL query:

SELECT name, age, grade FROM staff WHERE id=12345;

The idea of this statement is to get details about a single person from the ‘staff’ database table. As there are many records in the ‘staff’ table, we’re likely to run many similar queries which would differ only by a number in the end. For all those queries, there will be the same steps in the execution plan.

To prevent the database server from wasting resources on compiling the same execution plan again and again, we need to rewrite the query in a way which helps the server find out where the variables are.

Typically, DBMS use various kinds of placeholders to achieve that, like :var1, ?, $1, $named_arg. Our query would look like this:

SELECT name, age, grade FROM staff WHERE id=:var1;

Our query does not contain an actual id value to look for anymore but contains a reference to a var1, to be provided separately via a special interface.

Using SQL statements with variables helps you achieve a whole set of advantages:

1. Save latency for compilation and reduce load on DBMS hardware, as an execution plan can be easily cached and matched with the text of an SQL query.
2. Protect against SQL-injections. Inserting a malicious SQL as a query argument simply doesn’t work, because the value is not parsed.
3. Reduce code size. Only the request arguments change, but not the request text itself. Thus, no manual query string concatenation is necessary.

Prepared statements

Historically, YDB has had no other way to use queries with variables and leverage query plan cache other than using thePrepare statements interface (also called Prepared Statements and bind variables):

// stmt stores the query_id reference of the compiled query
stmt, _ := session.Prepare(ctx, `
    DECLARE $id as Int32;
    SELECT * FROM table WHERE id=$id;
`);
...
// repeatedly execute the query by query_id
res, _ := stmt.Execute(ctx, table.NewQueryParameters(
   table.ValueParam("$id", types.Int32Value(123456)),
));

It may seem that Prepare is a silver bullet.

What’s wrong with Prepare?

But Prepare statements also have some drawbacks, especially relevant for distributed DBMS such as YDB. It's very important to understand their lifecycle, and this part can be specific to a particular DBMS. So, in YDB:

• Prepare statements are generated within the session and exist while the session is "alive". In the case of YDB, a session is a single-threaded actor, which suits a distributed system environment. Sessions can be invalidated for some reason (as a result of server balancing or other internal activities on the DBMS side). And if the session suddenly disappears, then the requests prepared on it (statements) should also be invalidated (deleted, "forgotten", excluded for further use). Here we need to understand whether to invalidate a statement or not depending on error codes. In particular, if a query with an unknown query_id comes to YDB, then the NotFound error is returned.
• The client can’t rely on Prepare statement to be valid from application start to application shutdown. If, for some reason, the YDB node or the session on it is unavailable (the network "blinked", maintenance is underway, server balancing of sessions has occurred), then the prepared Prepare statement becomes unsuitable for executing the request. Here we have to prepare a new request for another session (and possibly another YDB node).
• We should take into account that if we prepared a request through Prepare, all subsequent Execute are routed to the same YDB node. This means that likely there may be a load skew (some nodes may be overloaded while some may be idle).
• All of our SDKs implement efficient client-side request balancing to make the most of the YDB cluster throughput. As well as the optimal distribution of the load among the nodes of the cluster due to the mechanisms of client balancing (see our blog post about client-side balancing in YDB). In case of Prepare statement client-side balancing does not work because the prepared statement is hard-wired to the session and node of YDB.

Seems too complicated, doesn’t it?

It is dangerous to use Prepare statements when a statement is an external variable relative to the returner.

// Some kind of repeater (from sdk or our own)
func queryRetry(stmt, args) (res, err) {
    for i := 0; i < 10; i++ {
        // stmt may be invalidated on one of the past attempts,
        // and we continue to re-trail on it
        res, err = stmt.Execute(args)
        if err == nil {
            return res, nil
        }
    }
    return nil, err
}
...
// danger here - stmt saved for later use
// and its status is not tracked in any way
stmt := sessionPool.Get().Prepare(query)
...
err := queryRetry(stmt, args)

If Prepare is called inside a retrier, then the risk of storing the statement for later use is eliminated, but an extra request to the server is required in each iteration of the retrier.

// Some kind of repeater (from sdk or our own)
func queryRetry(sessionPool, query, args) (res, err) {
    for i := 0; i < 10; i++ {
        session := sessionPool.Get()
        // if such a request has already been prepared at the session, then this will be a fast hop.
        // otherwise, the request will be honestly compiled
        stmt := session.Prepare(query)
        res, err = stmt.Execute(args)
        if err == nil {
            return res, nil
        }
    }
    return nil, err
}

And then how to effectively cache requests?

As for now, we don’t recommend using Prepare statements for query caching. YDB has a more efficient way to make a query compile once and multiple subsequent query executions using the compilation results. We suggest using the parameterized call Execute on the session with the KeepInCache flag set. In this case, the first request to the YDB node leads to the compilation of the request, and the compilation result is cached on the YDB node (on the node - not on the session). In YDB the query cache is LRU cache with a default limit of 1000. For most client applications, this limit is enough.

To simplify our example we can replace two consecutive calls <Prepare+Execute> with one Execute with the KeepInCache flag:

• with Prepare +Execute:

session := sessionPool.Get()
stmt := session.Prepare(query)
stmt.Execute(query, args, WithKeepInCache())

• with Execute+KeepInCache:

session := sessionPool.Get()
res, err = session.Execute(query, args, WithKeepInCache())

With Execute+KeepInCache, we get the advantage of client-side balancing, requests are evenly distributed across the YDB nodes and compiled on the first Execute request on the node, so the query cache is also duplicated on the YDB nodes. And we don't bother with the complexity of maintaining prepared queries on the client side with a distributed DBMS.

Get Myasnikov Aleksey’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

There are, of course, downsides to this approach too. If we have many unique requests then LRU cache eviction on the node might accidentally evict the ones that do repeat. For example, this could happen when a client program automatically concatenates parameters into the query text.

Strictly speaking, query caching via the Prepare call puts the query into the same LRU-cache on the node with the only difference: this cache is accessed via the query_id that comes in response to Prepare. And this query_id becomes invalid along with the session.

We strive to provide a user-friendly and transparent behaviour by default in our SDKs. This includes the server-side cache as well. The following are examples of explicit server-side cache management of compiled requests from our SDKs:

• in ydb-go-sdk:

The KeepInCache flag is set automatically (if at least one request argument is passed), to explicitly disable server caching of the request, we can pass the option WithKeepInCache(false)

err = db.Table().Do(ctx, func(ctx context.Context, s table.Session) (err error) {
    _, res, err = s.Execute(ctx, table.DefaultTxControl(), query,
        table.NewQueryParameters(params...),
        // uncomment if need to disable query caching
        // options.WithKeepInCache(false), 
    )
    return err
})

• in ydb-java-sdk:

The KeepInCache flag is set to true by default in ExecuteDataQuerySettings class object. You can disable KeepInCache flag if required:

CompletableFuture<Result<DataQueryResult>> result = session.executeDataQuery(
    query,
    txControl,
    params,
    new ExecuteDataQuerySettings()
        // uncomment if need to disable query caching
        // .disableQueryCache()
);

• in ydb-jdbc-driver:

The server cache is controlled by &keepInQueryCache=true&alwaysPrepareDataQuery=falseJDBC connection string parameters.

• in ydb-cpp-sdk:

To place a query in the cache, use the KeepInQueryCache method of the TExecDataQuerySettings class object.

NYdb::NTable::TExecDataQuerySettings execSettings;
// uncomment if need to disable query caching
// execSettings.KeepInQueryCache(true);
auto result = session.ExecuteDataQuery(
  query, TTxControl::BeginTx().CommitTx(), execSettings
)

• in ydb-python-sdk:

The KeepInCache flag is set automatically if at least one request argument is passed. This behavior cannot be overridden. If a server-side query cache is not required, it is suggested to use Prepare explicitly.

• in ydb-dotnet-sdk:

The KeepInQueryCache flag is set to true by default in ExecuteDataQuerySettings class object. You can disable KeepInQueryCache flag if required:

var response = await tableClient.SessionExec(async session =>
    await session.ExecuteDataQuery(
        query: query,
        txControl: txControl,
        settings: new ExecuteDataQuerySettings { KeepInQueryCache = false }
    ));

• in ydb-rs-sdk:

There is no separate method for compiling a request in Rust, the server cache is also not used yet but we’re going to implement it soon.

• in ydb-php-sdk:

The keepInCache flag is set to true by default in thequery class object. You can disable keepInCache flag if required:

$table->retryTransaction(function(Session $session){
    $query = $session->newQuery($yql);
    $query->parameters($params);
    $query->keepInCache(false);
    return $query->execute();
}, $idempotent);

• in ydb-nodejs-sdk:

The 4th argument to the session.executeQuery() method is an optional settings object of the ExecuteQuerySettings type. It has the keepInCache flag, but by default it is false. You can set it to true like this:

const settings = new ExecuteQuerySettings();
settings.withKeepInCache(true);
await session.executeQuery(..., settings);

Conclusion

In short, we recommend running parameterized queries with the KeepInCache flag for efficient caching of query compilation results to boost up performance of executing requests to YDB and reduce the complexity of code and thus cost of development and maintenance. It is important to note that we do not deprecate Prepare, it still continues to work. The article only gives a recommendation on how to leverage the YDB query cache in an easier and more reliable way.

If you have any difficulties or questions, please don’t hesitate to contact us via:

• GitHub
• Discord
• Telegram