Hints
A hint is an addition to the SQL standard that instructs the database engine on how to execute the query. Hints can be uesd to improve performance by forcing the optimizer to use a more efficient plan.
The following list demonstrates HINT keywords supported by DolphinDB.
| Keyword | flag | Description | Example |
|---|---|---|---|
| [HINT_LOCAL] | [1] | Obtains only the calculation results on the local nodes in a distributed cluster. | select [HINT_LOCAL] sum(*) from pt |
| [HINT_HASH] | [32] | Groups data with the hash algorithm by default
when performing group by. |
select [HINT_HASH] count(*) from t group by sym |
| [HINT_SNAPSHOT] | [64] | Queries data from a DFS table with a snapshot
engine registered with registerSnapshotEngine. |
select [HINT_SNAPSHOT] * from loadTable(dbName,tableName) |
| [HINT_KEEPORDER] | [128] | Keeps the order of output records consistent
with the input for each context by group. |
select [HINT_KEEPORDER] cumsu(vol) from t context by date, sym |
| [HINT_SEQ] | [512] | Executes queries on partitions serially to save the concurrency overhead when memory is limited. | timer select [HINT_SEQ] avg(vol) from t |
| [HINT_NOMERGE] | [1024] | Skips the merge step of intermediate results from map clause. Instead of returning the merged results in an in-memory table, only the handles of DFS tables are returned. | select [HINT_NOMERGE] price from pt context by ticker |
| [HINT_PRELOAD] | [4096] | [TSDB only] Loads all data into memory before filtering with where-conditions. | select [HINT_PRELOAD] sum(price) from t where volume > 100000 |
| [HINT_EXPLAIN] | [32768] | Prints the query execution plan to monitor the real-time performance and execution order of a SQL query for SQL performance tuning. | select [HINT_EXPLAIN] * from tb where id > 20 |
| [HINT_SORT] | [524288] | Uses sorting algorithm for data grouping
during group by operations. |
select [HINT_SORT] avg(price) from trades group by sym |
| [HINT_VECTORIZED] | [4194304] | Uses vectorization for data grouping during
group by operations. |
select [HINT_VECTORIZED] sum(price) from trades group by sym |