Local Query Execution

When connecting to an external relational database, you can request that Ultorg execute queries on your local computer, rather than on the external database. This will often improve query performance, and reduce load on the external server.

For databases with an only partially supported SQL dialect, local query execution may be enabled to get access to the full set of Ultorg's formula functions.

In current Ultorg versions, local query execution happens on a special PostgreSQL instance that comes bundled and pre-configured with the Ultorg desktop app.

Enabling and Disabling

To enable local query execution, select a data source and click Other Data Source Options in either its context menu or the Data menu. Then check Run Queries on Local Data Extracts:

The Run Queries on Local Data Extracts Setting.

The option is only configurable for SQL-based data sources. For other kinds of data sources, and for perspectives over multiple data sources, local query execution is always used.

Trivial queries, which merely show the contents of a single table, are still executed remotely.

Behavior

To facilitate local query execution, Ultorg creates an extract, i.e. a local copy, of each database table that is used in a perspective. This is done the first time each table is used. Ultorg will show extract loading status next to each original table in the external data source:

Indications of extract loading state, shown next to each source table in the Fields sidebar.

The actual generated extracts can be found in the Generated Extracts folder.

In an open perspective, the Refresh Data (Refresh Data icon, Ctrl+R/⌘R) action will include a refresh of extracts that are being used for local query execution.

Generated extracts are automatically deleted or recreated if the user changes or deletes the data source configuration (e.g. when changing import settings for CSV files).

Custom Table Extracts

You can create extracts manually if desired, of individual database tables. These extracts can include a smaller subset of columns, or have filters enabled to reduce the number of rows. The local query execution feature will detect such extracts and make use of them when possible.

When multiple extracts are present for a given table, extracts with fewer columns will be preferred for query execution purposes. In cases where a perspective's query can be satisfied with a filtered subset of data, relevant filtered extracts will be preferred over unfiltered ones.