SuiteQL performance tips that change the runtime

SuiteQL performance problems are rarely subtle. A query that takes 40 seconds and a query that takes 400 milliseconds usually differ by one clause. This is the checklist I work through, in the order that finds the problem fastest, with the rewrite for each.

Diagnose first: what kind of slow is it?

Rule 1: never wrap a filtered column in a function

This is the highest-yield fix in SuiteQL and it comes straight from Oracle behaviour. Applying TRUNC, TO_CHAR or arithmetic to a column in the WHERE clause means the database has to evaluate that expression for every candidate row before it can compare anything.

WHERE TRUNC(t.trandate) = TO_DATE('2026-06-30', 'YYYY-MM-DD')

WHERE t.trandate >= TO_DATE('2026-06-30', 'YYYY-MM-DD')
  AND t.trandate <  TO_DATE('2026-07-01', 'YYYY-MM-DD')

The same rule kills TO_CHAR(t.trandate,'YYYY-MM') = '2026-06' and t.trandate + 30 < SYSDATE. Move the arithmetic to the literal side: t.trandate < SYSDATE - 30 is fine because the column stays bare. Half-open ranges — >= start and < next start — also avoid the classic end-of-day bug where a datetime column at 14:32 falls outside <= '2026-06-30'. If you are computing those boundaries by hand every time, the fiscal period calculator will give you the period start and end for a date and a fiscal year start month.

Rule 2: keep BUILTIN.DF out of the WHERE clause

BUILTIN.DF() resolves an internal id to its display name. It is a per-row lookup. In the SELECT list that cost is proportional to the rows you return; in the WHERE clause it is proportional to the rows you examine, which can be the whole table.

-- Slow
WHERE BUILTIN.DF(t.entity) = 'Northwind Traders'

-- Fast
WHERE t.entity = 1042

-- Or resolve the id once, then filter
WHERE t.entity IN (SELECT id FROM customer WHERE companyname = 'Northwind Traders')

The same applies to BUILTIN.DF(t.status) in filters. Find the status code once with a GROUP BY t.status discovery query, then filter on the code.

Rule 3: filter the parent, then join the lines

transactionline is one of the largest tables in the account. If your filter only touches the line table, the join has to produce line rows before anything is discarded. Put the date and type filters on transaction as well, so the parent set is small before the join expands it.

SELECT t.tranid, BUILTIN.DF(tl.item) AS item, tl.quantity, tl.netamount
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type      = 'CustInvc'
  AND t.trandate >= TO_DATE('2026-06-01', 'YYYY-MM-DD')
  AND t.trandate <  TO_DATE('2026-07-01', 'YYYY-MM-DD')
  AND tl.mainline = 'F'
  AND tl.taxline  = 'F'

The join key matters too. transactionline.transaction = transaction.id is the native relationship. Joining on anything derived — a document number, a concatenation, a formatted date — turns a key lookup into a scan. The standard join paths are worth learning properly; SuiteQL join examples has them written out.

Rule 4: page with a cursor, not with OFFSET

Both the REST SuiteQL endpoint and the paged SuiteScript API let you walk results in pages. The trap is that skipping rows is not free — to return rows 40,001 to 41,000 the database still has to identify and discard the first 40,000. Page 1 is quick, page 41 is not, and the total cost of a full extract grows quadratically.

SELECT t.id, t.tranid, t.trandate, t.foreigntotal
FROM transaction t
WHERE t.type = 'CustInvc'
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND t.id > 0                      -- last id from the previous page
ORDER BY t.id

Take the highest id from each page and feed it back as the cursor. The filter stays selective, the sort stays on the key, and the runtime is flat. This is also restartable: if a scheduled script dies at page 37 it resumes from a stored id rather than from the beginning.

Rule 5: make the database do the arithmetic

The most expensive part of many extracts is the rows themselves. If the end product is a total per customer, do not pull 84,000 invoice rows and sum them in JavaScript. Group in SQL and return 300 rows. This cuts transfer time, memory in the script, and governance consumption at once.

• Aggregate in SQL — SUM, COUNT, GROUP BY, HAVING all work. Compute derived ratios in an outer select where you can reference the aliases.
• Never query inside a loop. One query per row is the classic way to exhaust a script's usage budget. Fetch the set once, keyed by id, and look it up in memory.
• Replace `NOT IN` with `NOT EXISTS`. NOT IN against a subquery that can return a null gives you zero rows with no error, and it optimises worse than the anti-join.
• Split `OR` across tables into `UNION ALL`. A predicate like t.memo LIKE '%rush%' OR tl.memo LIKE '%rush%' forces the widest possible scan; two selective queries unioned are usually far cheaper.
• Ask for the columns you need. SELECT * on the analytics tables drags back dozens of columns you discard, and it breaks silently when the schema shifts.

Rule 6: count before you fetch

The cheapest thing you can do before running an unbounded extract is find out how big it is. A COUNT(*) with the identical filter set tells you whether you are about to move 4,000 rows or 4 million, and it costs one round trip.

SELECT COUNT(*) AS rows_returned
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type      = 'SalesOrd'
  AND tl.mainline = 'F'
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')

If the count surprises you, your filters are wrong and the extract would have been wrong too. That is a better outcome than a timeout.

Governance and concurrency: the mechanism

Two separate ceilings catch people out, and they fail differently.

• Script governance. Each query call consumes usage units from the executing script's budget, and long-running work is subject to a time limit. A user event script that issues one query per line will die on a 400-line order while the same query is instant in a query tool. The fix is architectural: move bulk work to map/reduce, checkpoint, and batch your queries.
• Account concurrency. Web services and REST requests share a concurrency allowance across the whole account. Firing twenty parallel SuiteQL calls to "speed up" an extract can queue or reject requests, and it competes with the integrations already running. Sequential paging with a cursor is usually faster end-to-end than a parallel fan-out that gets throttled.
• Result-set caps. The non-paged call returns at most a documented number of rows and does not tell you it truncated. If a total looks suspiciously round, check whether you are seeing the cap rather than the data. Use the paged API for anything unbounded.

A truncated result is worse than a slow one, because it is a wrong number that looks fine. The error patterns that go with these ceilings are described in common SuiteQL errors explained.

How to measure SuiteQL performance without a query plan

You do not get EXPLAIN PLAN and you do not get index statistics, so optimisation is empirical. That is workable if you are disciplined about it.

1. 1.Change one clause at a time. Two changes at once and you learn nothing about either.
2. 2.Time both variants back to back, in the same account, minutes apart. Account load varies enough through the day that yesterday's timing is not a baseline.
3. 3.Record the row count with the timing. A query that got faster because it now returns fewer rows has not got faster, it has got wrong.
4. 4.Test at production volume. A sandbox refreshed six months ago is a different query plan. Where you can only test in sandbox, at least compare COUNT(*) between environments so you know the factor you are extrapolating by.
5. 5.Retest as the production role. Permission filtering changes both the row count and the work done.

When the query is fine and the process is the problem

Sometimes the honest answer is that SuiteQL is the wrong tool for the job. A weekly list that a controller opens, sorts and clicks through does not need a hand-tuned query and a custom UI — it needs a saved search, for the reasons set out in SuiteQL vs saved search. And a question asked once does not deserve an optimisation pass at all; it deserves an answer. Ready-made recipes for the common ones are in the SuiteQL transaction queries cookbook.

That last category is what ERPray is for. Ask a question in plain English, get the number computed live from your own account, and see the SuiteQL it ran — bounded, filtered and printed underneath, so you can check the date range and the mainline filter before you use the figure. When a query proves worth keeping, you already have the tuned text to hand to your developer. If you would rather export the raw rows and work on them elsewhere, the JSON to CSV converter turns a REST response into something a spreadsheet will open.