Why is my query slow or unresponsive?

The order of operations, excessive row change notifications, inefficient memory use, etc. may all slow down a query.

Look at performance details

The Query Monitor includes relevant information about a query's status, but the QueryOperationPerformanceLog in DbInternal provides further performance details on query operations, such as duration and memory usage.

QOPL = db.i("DbInternal", "QueryOperationPerformanceLog")

Each query is broken up into its component parts, allowing in-depth understanding of the performance impacts of each individual operation for a query.

If a query is not updating as quickly as expected, or the UI seems slow, you can view the UpdatePerformanceLog in DbInternal for information about incremental update operations in the Live Table Monitor loop.

UPL = db.i("DbInternal", "UpdatePerformanceLog")

Both of these tables include a "Description" or "EntryDescription" column with human-readable information. For example, you can see when tables or partitions have been added, deleted, or moved. The majority of the entries in this column indicate each operation performed on a table (e.g., join, sort, where), and looking across the row to columns such as "Duration" or "WasInterrupted" may determine which specific operations are slow and offer hints as to how to resolve the issue.

Determine how your data is grouped

The following debugging procedure helps you make sure the data is grouped when you expect it to be.

The query below accesses billions of rows:

db.t("FeedOS", "OPRAQuoteL1").where("Date.contains(`2019`)").where("LocalCodeStr = `SPY`")

There are a few things going on here.

The root table db.t("FeedOS", "OPRAQuoteL1") is basically stored as a tree. The data is partitioned upon the Date column. The leaf nodes should be grouped by some other columns, including LocalCodeStr.

When the query is executed without a Date filter, you are processing all parts of a possibly multi-terabyte table. Option quotes are quite large.

You can use the getMeta() method to indication which columns are used to group the leaf nodes:

db.t("FeedOS", "OPRAQuoteL1").getMeta()

When the intraday data is converted to historical data, it is reordered (in other words, "grouped") so that it is quicker to index into the grouped columns. If a where filter on LocalCodeStr is slow, the person that set up this table may have forgotten to set the column as grouping.

Because the option quotes are an enormous data set, you may need a query that runs on each day and outputs a summary table of what you want. For example, you could save a partitioned user table for downsampled "SPY" data.

As discussed elsewhere in the documentation, many factors affect a query's performance: how data is grouped, how it's organized on disk, and the way the query is written (i.e., the partitioning column should always be first in a query). For more troubleshooting strategies, see our Troubleshooting Queries guide.