Parameterized queries and recompilation
Query compilation takes time and resources, so YDB provides a compile cache. The compilation result is stored in the cache on a cluster node and reused only when the query text matches exactly. If your application builds YQL with string concatenation or formatting, each new set of values produces a different text, and the server recompiles it even when the SQL structure is the same.
Consequences:
- higher latency at the compilation stage;
- increased CPU load on nodes;
- with many unique query texts, reuse of the limited per-node cache gets worse.
A parameterized query helps avoid extra compilation: the YQL text stays fixed, and input values are passed separately via named parameters (for example, $userId). Below, both approaches are compared on the same example.
Embedding values in the query text
The application inserts values directly into the query text:
SELECT id, name FROM users WHERE id = 123 AND status = "active";
SELECT id, name FROM users WHERE id = 456 AND status = "inactive";
The queries differ only in values, but the texts are different — for the server these are two different queries, and each one is compiled separately.
Passing values as parameters
The application passes values separately from the text; the query text does not change:
DECLARE $userId AS Uint64;
DECLARE $status AS Utf8;
SELECT id, name FROM users WHERE id = $userId AND status = $status;
The server handles calls to such a query differently:
- First call — the server compiles the query and stores the result in the cache on the node.
- Later calls — if the text is already in the cache, the ready result is reused: only
$userIdand$statuschange; recompilation is not required.
Only values can be passed as parameters: you cannot parameterize a table name or sort order; changing them changes the query text.
See Query compile cache for cache contents, and Top queries for compilation time when queries run (the CompileDuration field).
Cache size and settings, the KeepInCache flag, DECLARE syntax, and passing parameters from code are described in Parameterized queries in the YDB SDK reference.