The query settings dialog tunes how the query behind a table's load runs. Open it from the table popup or the Batch edit grid, choose a preset or edit the settings text, set the switches, and save. The settings belong to the table you opened the dialog from.
The dialog is titled Query Settings Configuration. It has two views, Editor and Settings Reference, and opens on Editor each time. While the defaults load, it reads Loading defaults....
The Editor view holds the settings text. Picking a preset replaces the text:
| Preset | What it does |
|---|---|
| Balanced | Default - balanced speed and memory. |
| Turbo | Faster - uses more RAM. |
| Eco | Saves RAM - slower. |
Balanced is the default preset. Editing the settings text takes the mode to Custom; to return, pick a preset or click the reset icon (Reset to default), which appears while the mode is not Balanced.
The text is one parameter per line, in the form key = value. The dialog compares the text to the presets ignoring line order, comments and trailing commas.
The dialog also holds the switches that shape the query. Which switches appear depends on where you opened the dialog from.
| Switch | What it does |
|---|---|
| Distinct Rows | Applies SELECT DISTINCT when reading from the source, removing duplicate rows before they are loaded into the staging table. It can increase query time. |
| Partition Optimization | Applies ORDER BY on the Snapshot column during inserts. It reduces partition parts and background merges, and can slightly increase insert time. |
| Recompile Query Plan | Appends OPTION (RECOMPILE) to the source query. It applies to MSSQL and Azure SQL Database sources; on other sources it stays disabled with the note This table's source is not MSSQL, so the option is unavailable. |
| Empty TARGET_OUTPUT tables after successful ETL run | After a successful scheduled or manual run, the run's staging copies are emptied, which frees parts and memory. Test runs never empty them, and a looping run empties them only after its last chunk. |
For a Lake table, Distinct Rows is off by default, Partition Optimization is on by default, and Recompile Query Plan is off by default.
Cancel closes the dialog and discards what you changed in it. Saving a table's settings needs Add/Modify Lake Objects; see Who can do what in the Lake.
The Settings Reference view lists every parameter behind the settings, grouped into a read side (Query Execution Settings, Network & Timeout Settings, HTTP Retry & Resilience Settings, Performance & Resource Settings) and a write side (Async Insert Settings, Insert Block Size Settings, Parts Management Settings).
Each row shows the parameter, its default and what it does. The copy control, Copy {parameter}, writes {param} = to your clipboard, ready to paste; the check mark it shows resets after two seconds.
Some defaults worth knowing:
| Parameter | Default | Note |
|---|---|---|
max_threads | 8 | |
max_execution_time | 86400 | (24 Hours) |
max_memory_usage | 10737418240 | A run over the limit fails with Memory limit exceeded. |
async_insert | 1 | |
wait_for_async_insert | 1 | (recommended) |
insert_deduplicate | 1 | |
parts_to_throw_insert | 300 | An insert into a partition that reaches this many active parts is refused with Too many parts. |
The History button, whose tooltip reads Query settings history, opens the versions kept for the table's settings. Use it to undo a bad save or reuse an earlier tuning.
Restore stays disabled while a version is Identical to the current settings. The list reads No settings history yet while the table has no saved versions, Loading history… while it loads, and Could not load query settings history when it cannot load. Before you pick a version, the pane reads Select a version to view it; a version with nothing saved reads No settings saved - the default applies.
On Table > General in the table popup, click Configure on the Query execution settings tile. In Batch edit, the grid's Query Settings cell opens the same dialog. See Configure a table and The Batch edit grid.
Balanced is the default and balances speed and memory. Turbo runs faster and uses more RAM. Eco saves RAM and runs more slowly. Editing the settings text takes the mode to Custom.
The settings text no longer matches a preset exactly, which happens as soon as you edit it. To return to a preset, pick one, or click the reset icon (Reset to default).
Every parameter behind the settings, in read and write groups, with each parameter's default, what it does, and a copy control. The view is reference material only; it does not change your settings.
It applies SELECT DISTINCT when reading from the source, so duplicate rows are removed before they load. It can make the query run longer.
It applies ORDER BY on the Snapshot column during inserts, which reduces partition parts and background merges. It can slightly increase insert time.
Only for MSSQL and Azure SQL Database sources. On any other source the switch is disabled and explains why: This table's source is not MSSQL, so the option is unavailable.
After a successful scheduled or manual run, it empties the run's staging copies, which frees parts and memory. Test runs never empty them, and a looping run empties them only after its last chunk.
A value in the settings text was refused, so nothing was saved and the dialog stays open with the reason. Correct the value and click Save Settings again.
No. A restore only loads the version into the dialog. The notification reads Restored version v{N} and asks you to click Save Settings to apply it.
The version is identical to what the dialog already holds, so there is nothing to restore. The reason reads Identical to the current settings.
No. The dialog compares the settings text to the presets ignoring line order, comments and trailing commas, so the same values in another order still match.