Skip to main content

Query performance

Some query patterns make a J1QL query slow, make it fail, or back up the query queue for your account. This page covers how to recognize a heavy query, the patterns that most often cause one, and the limits worth knowing before you write a large query.

How to tell a query is too heavy​

Watch for these signs rather than guessing at a size:

  • It fails with a memory-limit error. When a query needs more memory than your account's query limit allows, it fails with an error naming db.memory.transaction.total.max. Seeing this error is a sign the query is too heavy and needs a narrower filter or a lighter pattern.
  • It takes longer than 30 seconds. A query that may run longer than 30 seconds should use a deferred response so the request does not time out.
  • A subquery hits its return limit. A subquery returns at most 5,000 values; see Subqueries. Beyond that limit, results may be incomplete.

If you see any of these, look at the patterns below before reaching for a bigger LIMIT — the shape of the query usually matters more than its size.

Heavy patterns and their rewrites​

Three query shapes are the most common cause of a heavy query. Each is shown below as a heavy example and a lighter rewrite that asks for the same kind of answer with less work for the system. (~= is "contains", ^= is "starts with" and $= is "ends with" — see Comparisons.)

OR'd contains matches on one property​

Chaining several ~= ("contains") checks on the same property together with OR gives the query service one separate match to evaluate for each value.

Heavy:

FIND Host WITH hostname ~= "example-web-01" OR hostname ~= "example-web-02" OR hostname ~= "example-web-03"

Lighter rewrite — when the values share a pattern, match them with one regex filter instead of OR'ing several ~= checks. Regex alternation (|) isn't supported in J1QL, so cover the shared, varying part with a character class instead; see Regular expressions for supported regex syntax:

FIND Host WITH hostname = /example-web-0[1-3]/

ORDER BY over a large unfiltered result​

Sorting with ORDER BY before narrowing the result means the engine has to sort everything it finds, not just what you actually need.

Heavy:

FIND Host AS h
THAT HAS Finding AS f
RETURN f.displayName, f.severity
ORDER BY f.severity

Lighter rewrite — filter both sides of the traversal first, then sort, then cap the result:

FIND Host WITH active = true AS h
THAT HAS Finding WITH open = true AS f
RETURN f.displayName, f.severity
ORDER BY f.severity
LIMIT 100

A multi-hop pattern with no narrow starting filter​

A traversal with several hops and no filter on its starting entity asks the engine to walk the graph outward from every matching node, however many there are.

Heavy:

FIND Host
THAT HAS Finding
THAT RELATES TO Vulnerability
THAT RELATES TO CVE
RETURN CVE.displayName

Lighter rewrite — anchor the traversal with a narrow filter on the first hop:

FIND Host WITH tag.AccountName = "team-a" AS h
THAT HAS Finding WITH open = true
THAT RELATES TO Vulnerability
THAT RELATES TO CVE
RETURN CVE.displayName

Limits to know​

  • 250-row default. A query with no LIMIT returns up to 250 rows by default. See Pagination and sorting.
  • Cursors over SKIP and LIMIT. When paging through a large result, prefer cursors over SKIP and LIMIT. See Entity and relationship queries.
  • Deferred responses for long queries. A query that may take longer than 30 seconds should use a deferred response (deferredResponse: FORCE in the API) so the request does not time out. See Entity and relationship queries.
  • 5,000-value subquery limit. A subquery returns at most 5,000 values. See Subqueries.
  • The memory-limit error. A query that needs more memory than your account's query limit allows fails with an error naming db.memory.transaction.total.max.

Checklist​

Before you run a large or unfamiliar query, check:

  • Does an OR chain several ~= matches on the same property? Combine them into one regex instead.
  • Does ORDER BY run over an unfiltered or lightly filtered result? Filter first, sort second, and add a LIMIT.
  • Does a multi-hop traversal start without a narrow filter? Anchor it with one.
  • Are you paging with SKIP and LIMIT? Use a cursor instead.
  • Might the query run longer than 30 seconds? Use a deferred response.
  • Did the query fail naming db.memory.transaction.total.max? Narrow the filter or rewrite the pattern before retrying.