Optimizing High-Concurrency Point Queries in Doris with Row Store, Short Query Path, and PreparedStatement

Doris is built on a column‑store engine, and in high‑concurrency scenarios users often need to retrieve whole rows, which can cause large random I/O and heavy query planning for simple point queries. To address this, row store, a short query path, and PreparedStatement support have been introduced.

Row Store : Enable row store mode for an OLAP table by setting the property "store_row_column" = "true" in the CREATE TABLE statement. This stores rows encoded in a separate column and requires additional disk space.

Point‑Query Optimization in Unique Model : When creating a UNIQUE table, enable "enable_unique_key_merge_on_write" = "true" together with "store_row_column" = "true". This activates a short‑circuit path that executes the query with a single RPC. Example DDL:

CREATE TABLE `tbl_point_query` (
    `key` int(11) NULL,
    `v1` decimal(27, 9) NULL,
    `v2` varchar(30) NULL,
    `v3` varchar(30) NULL,
    `v4` date NULL,
    `v5` datetime NULL,
    `v6` float NULL,
    `v7` datev2 NULL
) ENGINE=OLAP
UNIQUE KEY(`key`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`key`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1",
    "enable_unique_key_merge_on_write" = "true",
    "light_schema_change" = "true",
    "store_row_column" = "true"
);

Key notes:

Enable enable_unique_key_merge_on_write for fast primary‑key point lookups.

Queries that contain only the primary‑key equality condition (e.g., SELECT * FROM tbl_point_query WHERE key = 123) will use the short path.

Turn on light_schema_change because the optimization relies on column unique IDs.

Only single‑table key‑equality queries are supported; joins and subqueries are not.

Row store increases storage; after Doris 2.1 you can specify partial row‑store columns via "row_store_columns"="key,v1,v2" to save space.

Example point‑query using the partial row store:

SELECT key, v1, v2 FROM tbl_point_query WHERE key = 1;

Using PreparedStatement : To reduce SQL parsing and expression evaluation overhead, Doris FE provides a MySQL‑compatible PreparedStatement feature (currently only for primary‑key point queries). When enabled, the SQL and its expressions are pre‑computed and cached at the session level, yielding up to 4× performance improvement.

JDBC URL example:

url = jdbc:mysql://127.0.0.1:9030/ycsb?useServerPrepStmts=true

PreparedStatement usage example:

// use '?' for placeholders, reuse the statement
PreparedStatement readStatement = conn.prepareStatement("select * from tbl_point_query where key = ?");
...
readStatement.setInt(1, 1234);
ResultSet resultSet = readStatement.executeQuery();
...
readStatement.setInt(1, 1235);
resultSet = readStatement.executeQuery();
...

Enabling Row Cache : Doris provides a page‑level cache for column data. To improve row‑cache hit rate for row‑store data, enable row cache via BE configuration: disable_storage_row_cache – whether to enable row cache (default off). row_cache_mem_limit – percentage of memory allocated to row cache (default 20%).

Performance Optimization Tips :

Increase the number of Observer nodes to boost query processing capacity.

Use JDBC load‑balancing or other load‑balancing solutions (e.g., Nginx, ProxySQL) when FE CPU becomes a bottleneck.

Direct point‑query traffic to Observer nodes to reduce load on the FE master and stabilize latency.

Q&A :

How to verify short‑circuit usage? Run EXPLAIN and look for SHORT-CIRCUIT in the plan.

How to confirm PreparedStatement is effective? Check Stmt=EXECUTE() in fe.audit.log.

Can non‑primary‑key queries use this optimization? No, only key‑equality queries without joins or subqueries.

Does useServerPrepStmts help regular queries? It only works for primary‑key point queries.

Is a global optimizer setting required? No, Doris automatically selects the best execution path when using PreparedStatement.