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
LIMITreturns up to 250 rows by default. See Pagination and sorting. - Cursors over SKIP and LIMIT. When paging through a large result, prefer cursors over
SKIPandLIMIT. 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: FORCEin 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
ORchain several~=matches on the same property? Combine them into one regex instead. - Does
ORDER BYrun over an unfiltered or lightly filtered result? Filter first, sort second, and add aLIMIT. - Does a multi-hop traversal start without a narrow filter? Anchor it with one.
- Are you paging with
SKIPandLIMIT? 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.