Queries that embed their values are re-parsed on every call (Cypher ~10x, SQL ~5x) and push parameterized statements out of the LRU statement cache: parameterize literals before the cache lookup?

[tae898]

Summary

Follow-up to #8132, where you showed that parameters remove the Cypher/SQL gap and advised switching our harness to them, which we are doing at our next re-pin. This is the other side of that finding, for the applications that cannot easily do the same: generated queries (text-to-Cypher from an LLM, GraphRAG tools, query builders) usually embed their values in the text. Two measurements, then a question.

1. Each embedded value is a new cache key, so the query is parsed and planned from scratch every time: an indexed point lookup costs about 10x in Cypher and about 5x in SQL against the same query with a parameter.
2. Those one-off texts evict the application's parameterized statements, because the Cypher and SQL statement caches are LRU at 300 entries. With 400 distinct embedded-value lookups between two calls of a parameterized lookup, the parameterized one gets 4x to 8x slower in SQL and 8x to 14x slower in Cypher than with 100 between.

Measurements

Embedded engine API, one database, 20,000 Person vertices with a unique index on id, 26.10.1-SNAPSHOT (5b040a438e), laptop, 4 cores, p50:

lookup	Temurin 21	Temurin 25 + COH
Cypher, value embedded: MATCH (p:Person) WHERE p.id = 12345 RETURN p.name, p.age	0.910 ms	0.778 ms
Cypher, $id	0.086 ms	0.083 ms
SQL, value embedded: SELECT name, age FROM Person WHERE id = 12345	0.456 ms	0.433 ms
SQL, :id	0.079 ms	0.085 ms

The parameterized lookup (MATCH (p:Person {id: $id}) RETURN p.name, and SELECT name FROM Person WHERE id = :id) alone, then with K distinct embedded-value lookups (each a new text) between its calls, after a warm-up, runs in the order 0, 400, 100, 0, 400. The K = 0 column is the second pass (the first was still warming up, 0.015 to 0.025 ms); the 400 column shows both passes:

parameterized lookup	K = 0	K = 100 (fits the cache)	K = 400 (overflows it)
Cypher, Temurin 21	0.0044 ms	0.0098 ms	0.122 / 0.080 ms
Cypher, Temurin 25 + COH	0.0044 ms	0.0098 ms	0.135 / 0.082 ms
SQL, Temurin 21	0.0060 ms	0.0084 ms	0.064 / 0.036 ms
SQL, Temurin 25 + COH	0.0066 ms	0.0088 ms	0.066 / 0.036 ms

Answers are checked on every call. Both repros are single Java files against the engine jars (below).

For context, the same point lookup over Bolt with one client for all three engines (the Python neo4j driver, each server in its own container, value embedded vs $id): Memgraph 1.04 vs 1.28 ms (no penalty), Neo4j 10.47 vs 2.71 ms (3.9x), ArcadeDB's Bolt server 1.96 vs 1.35 ms (1.45x, most of the parse cost hidden behind about 1.2 ms of client and network time). With a parameter, ArcadeDB's Bolt server is level with Memgraph.

Cause

CypherStatementCache.getParsed() keys on the query text with only a trailing semicolon stripped (CypherStatementCache.java:80-82), and so does CypherPlanCache.get() (CypherPlanCache.java:75) and the SQL StatementCache.get() (StatementCache.java:59). Both statement caches are LRU (LRUCache, and a LinkedHashMap with removeEldestEntry), sized by arcadedb.opencypher.statementCache and arcadedb.sqlStatementCache, 300 each. The Cypher plan cache is frequency-based (MostUsedCache), which protects the hot plan, but the statement parse in front of it is redone once the statement is evicted.

Question and suggestion

Would you consider stripping value literals before the cache lookup? That is, replace number and string literals with generated parameters at the token level, use the stripped text as the cache key, and bind the extracted values, so that ... p.id = 12345 and ... p.id = 67890 share one parsed statement and one plan. Memgraph shows no penalty for embedded values in the Bolt comparison above, which is the behavior this would give. The literals that shape a plan rather than filter it (LIMIT, SKIP, perhaps a literal in a label position) would stay as they are, and a setting could turn it off. I have not prototyped it, and whether a shared plan is always safe here is your call: the index choice depends on the property rather than the value, but I have not checked every planner rule.

Short of that, the churn half has a user-side mitigation, raising arcadedb.opencypher.statementCache and arcadedb.sqlStatementCache, which could be worth a line in the docs next to the advice to use parameters.

LiteralCacheChurn.java

import com.arcadedb.database.Database;
import com.arcadedb.database.DatabaseFactory;
import com.arcadedb.query.sql.executor.ResultSet;
import java.util.*;

/**
 * Does a stream of queries that embed their values evict a well-behaved parameterized query from the statement cache?
 *
 * The OpenCypher and SQL statement caches are LRU (300 entries by default) and keyed on the exact query text, so each
 * distinct embedded value adds an entry. The parameterized lookup is timed alone, then with K distinct embedded-value
 * lookups run between each of its calls. Answers checked on every call.
 *
 *   java -cp 'lib/*:cls' LiteralCacheChurn <dbPath>
 */
public class LiteralCacheChurn {
  static final int N = 20_000, CALLS = 300;

  public static void main(String[] a) {
    try (DatabaseFactory f = new DatabaseFactory(a[0])) {
      if (f.exists()) f.open().drop();
      final Database db = f.create();
      db.command("sql", "CREATE VERTEX TYPE Person");
      db.command("sql", "CREATE PROPERTY Person.id INTEGER");
      db.command("sql", "CREATE INDEX ON Person (id) UNIQUE");
      db.begin();
      for (int i = 0; i < N; i++) {
        db.newVertex("Person").set("id", i).set("name", "p" + i).save();
        if ((i + 1) % 5000 == 0) { db.commit(); db.begin(); }
      }
      db.commit();
      for (final String lang : new String[] { "cypher", "sql" }) {
        final String param = lang.equals("cypher") ? "MATCH (p:Person {id: $id}) RETURN p.name AS n" : "SELECT name AS n FROM Person WHERE id = :id";
        // WARM-UP FIRST: the first pass otherwise measures the JIT, not the cache (seen on the first run of this repro)
        for (int w = 0; w < 5000; w++)
          try (ResultSet rs = db.query(lang, param, Map.of("id", w % N))) { rs.next(); }
        for (final int k : new int[] { 0, 400, 100, 0, 400 }) {
          final Random r = new Random(11);
          final List<Double> lat = new ArrayList<>();
          int litCounter = 0;
          for (int c = 0; c < CALLS + 20; c++) {
            for (int j = 0; j < k; j++) {  // K distinct embedded-value lookups between each parameterized call
              final int id = (litCounter++ * 7919) % N;
              final String q = lang.equals("cypher") ? "MATCH (p:Person {id: " + id + "}) RETURN p.name AS n" : "SELECT name AS n FROM Person WHERE id = " + id;
              try (ResultSet rs = db.query(lang, q)) { rs.next(); }
            }
            final int id = r.nextInt(N);
            final long s = System.nanoTime();
            try (ResultSet rs = db.query(lang, param, Map.of("id", id))) {
              if (!("p" + id).equals(rs.next().getProperty("n"))) throw new IllegalStateException("wrong answer");
            }
            if (c >= 20) lat.add((System.nanoTime() - s) / 1e6);
          }
          Collections.sort(lat);
          System.out.printf("RESULT %-6s parameterized lookup, %3d embedded-value lookups between calls: p50 %.4f ms  p90 %.4f ms%n",
              lang, k, lat.get(lat.size() / 2), lat.get(lat.size() * 9 / 10));
        }
      }
      db.close();
    }
  }
}

CypherPointLookupParams.java (the four-arm repro from #8132)

import com.arcadedb.database.Database;
import com.arcadedb.database.DatabaseFactory;
import com.arcadedb.query.sql.executor.ResultSet;

import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.List;

/**
 * An indexed point lookup costs about 1.9x in Cypher versus the same lookup in SQL.
 *
 * Same database, same unique index, same records, same iteration count; only the
 * query language differs. Both scale sub-linearly with the corpus, so both use
 * the index -- the gap is a constant factor in the Cypher path, not a planning
 * difference.
 *
 *   javac -cp '/home/arcadedb/lib/*' CypherPointLookupParams.java
 *   java -cp '/home/arcadedb/lib/*:.' -Drepro.dir=/data -Drepro.n=20000  CypherPointLookupParams
 *   java -cp '/home/arcadedb/lib/*:.' -Drepro.dir=/data -Drepro.n=200000 CypherPointLookupParams
 */
public class CypherPointLookupParams {
  static final int N = Integer.getInteger("repro.n", 20000);
  static final int ITERS = Integer.getInteger("repro.iters", 300);
  static final int WARMUP = 30;

  public static void main(String[] args) throws Exception {
    final Path dir = Path.of(System.getProperty("repro.dir", "/data"), "cypherpoint" + N);
    deleteTree(dir);
    Files.createDirectories(dir.getParent());

    try (DatabaseFactory factory = new DatabaseFactory(dir.toString())) {
      final Database db = factory.create();
      db.command("sql", "CREATE VERTEX TYPE Person");
      db.command("sql", "CREATE PROPERTY Person.id INTEGER");
      db.command("sql", "CREATE INDEX ON Person (id) UNIQUE");

      db.begin();
      for (int i = 0; i < N; i++) {
        db.command("sql", "INSERT INTO Person SET id = " + i
            + ", name = 'p" + i + "', age = " + (20 + i % 50));
        if (i % 5000 == 4999) { db.commit(); db.begin(); }
      }
      db.commit();

      final double[] cyLit = time(db, true,  false);
      final double[] sqLit = time(db, false, false);
      final double[] cyPar = time(db, true,  true);
      final double[] sqPar = time(db, false, true);
      db.close();

      System.out.printf("%n  n=%d, %d timed iterations per arm%n", N, ITERS - WARMUP);
      System.out.printf("  %-34s %9s %9s%n", "", "p50 ms", "p99 ms");
      System.out.printf("  %-34s %9.4f %9.4f%n", "Cypher, embedded literal", cyLit[0], cyLit[1]);
      System.out.printf("  %-34s %9.4f %9.4f%n", "SQL, embedded literal",    sqLit[0], sqLit[1]);
      System.out.printf("  %-34s %9.4f %9.4f%n", "Cypher, $id parameter",    cyPar[0], cyPar[1]);
      System.out.printf("  %-34s %9.4f %9.4f%n", "SQL, :id parameter",       sqPar[0], sqPar[1]);
      System.out.printf("%n  cypher/sql with literals:   %.2fx%n", cyLit[0] / sqLit[0]);
      System.out.printf("  cypher/sql with parameters: %.2fx%n", cyPar[0] / sqPar[0]);
      System.out.printf("  parameters vs literals, Cypher: %.2fx faster%n", cyLit[0] / cyPar[0]);
      System.out.printf("  parameters vs literals, SQL:    %.2fx faster%n", sqLit[0] / sqPar[0]);
    }
  }

  /** The four arms: each language, with an embedded literal and with a parameter. */
  static double[] time(final Database db, final boolean cypher) {
    return time(db, cypher, false);
  }

  static double[] time(final Database db, final boolean cypher, final boolean params) {
    final List<Double> lat = new ArrayList<>();
    for (int k = 0; k < ITERS; k++) {
      final int id = k % N;
      final String lang = cypher ? "opencypher" : "sql";
      final String q = params
          ? (cypher ? "MATCH (p:Person) WHERE p.id = $id RETURN p.name, p.age"
                    : "SELECT name, age FROM Person WHERE id = :id")
          : (cypher ? "MATCH (p:Person) WHERE p.id = " + id + " RETURN p.name, p.age"
                    : "SELECT name, age FROM Person WHERE id = " + id);
      final long t0 = System.nanoTime();
      try (ResultSet rs = params ? db.query(lang, q, java.util.Map.of("id", id))
                                 : db.query(lang, q)) {
        while (rs.hasNext()) rs.next();
      }
      if (k >= WARMUP) lat.add((System.nanoTime() - t0) / 1_000_000.0);
    }
    final double[] a = lat.stream().mapToDouble(Double::doubleValue).sorted().toArray();
    return new double[] { a[a.length / 2], a[(int) (a.length * 0.99)] };
  }

  static void deleteTree(final Path p) throws Exception {
    if (!Files.exists(p)) return;
    try (var w = Files.walk(p)) {
      w.sorted((x, y) -> y.getNameCount() - x.getNameCount())
       .forEach(f -> { try { Files.deleteIfExists(f); } catch (Exception ignored) { } });
    }
  }
}

[lvca]

Thanks for the detailed measurements and the repro - both are reproducible against StatementCache.get() (engine/src/main/java/com/arcadedb/query/sql/parser/StatementCache.java), CypherStatementCache.getParsed() and CypherPlanCache.get(): all three key on the query text (trailing ; stripped) in a fixed-size LRU/frequency cache, exactly as described.

I've split the proposed fix (parameterize literals before the cache lookup) into a separate issue, #8307, rather than implementing it here. Not because it's a bad idea - it's the right fix - but because it touches both the SQL and Cypher grammars and its safety depends on an exhaustive audit of every planner rule that reads a literal's value (not just ? support, which is already correct per #8288's fix). That's a bigger design commitment than the batch this issue came in with, and deserves sign-off before someone starts rewriting token streams ahead of two parsers.

Available today, no code change: arcadedb.opencypher.statementCache and arcadedb.sqlStatementCache (both default 300) are runtime settings - SET DATABASE SETTING sqlStatementCache = <n> or the equivalent Cypher key - so anyone hitting the churn you measured can raise them immediately. I'll leave a docs pointer to that alongside the parameter-vs-literal guidance.

Leaving this issue open, tracked by #8307 for the actual fix.

[tae898]

Thanks, #8307 as its own design item makes sense, and the cache-size settings are good to know for anyone hitting this today.

For our benchmark we will do what your parameter-vs-literal guidance says: at our next re-measure every timed statement binds its values, on every engine that supports parameters, not only ArcadeDB. That takes the literal path out of the measurement rather than giving it a bigger cache, and it is what an application would write anyway. We will follow #8307 from here.

[lvca]

Fixed in #8677 (26.10.1) for the cache churn half: the SQL and Cypher statement caches now protect statements that are hit repeatedly, so a burst of one-off texts with embedded values no longer evicts your parameterized queries (in your test the parameterized lookup stays fast with 400 embedded lookups in between). Embedded values still cost a parse each; use parameters, or raise sqlStatementCache / opencypher.statementCache. Literal parameterization stays tracked in #8307.

---

❤️ Thanks for helping make ArcadeDB better! ArcadeDB is free and open source, sustained by its community. If it's useful to you or your company, please consider becoming a sponsor to keep development going.

[tae898]

Re-ran LiteralCacheChurn.java on the merge bfb9bc845a. With 400 embedded-value lookups between its calls, the parameterized lookup now takes, over the two measured passes, 0.020 and 0.013 ms in Cypher and 0.025 and 0.018 ms in SQL on Temurin 25 with compact headers (0.018 and 0.013, 0.029 and 0.018 on Temurin 21), against 0.160 and 0.106 / 0.104 and 0.053 ms on 66a82a8fd0. Thanks; the literal half stays with #8307, and our benchmark moves to bound values at its next re-measure.