SuiteQL vs N/search: Which One Is Actually Faster?

SuiteQL vs N/search: Which One Is Actually Faster?

Introduction

SuiteQL and N/search read the same NetSuite data, but they behave very differently once the record count climbs. Here is what actually decides the winner, with the governance numbers and the limits that catch people out.

Most SuiteScript performance problems have nothing to do with which API you picked. They come from asking NetSuite the wrong question.

Every NetSuite team has had this argument. One developer swears SuiteQL is faster. Another says saved searches are tuned by Oracle and you should leave them alone. Both are half right, which is why the argument never ends.

We have rewritten a lot of slow scripts over the years, and the honest answer is that neither API is universally faster. What matters is the shape of the question you are asking and how many round trips you make to get an answer. Get that right and either one performs well. Get it wrong and both crawl.

Here is how we decide, with the numbers.

They both hit the same database

This is the part people miss. N/search and SuiteQL read from the same Oracle database underneath NetSuite. There is no secret fast lane.

The difference is the layer in between. N/search goes through the saved search engine, which builds the query for you, applies its own joins and handles permissions the way the UI does. SuiteQL sends your SQL through with far less standing in the way.

So the real question is not which pipe is faster. It is how much work you are asking the database to do, and how many times you are asking.

Governance units are almost identical

People assume SuiteQL is cheaper on governance. Per call, it is not.

Two things fall out of that table.

First, a single SuiteQL costs the same 10 units as a single search. Swapping one for the other saves you nothing on its own.

Second, search.lookupFields at 1 unit is still the cheapest read in NetSuite. If you only need a few fields off one record, use it and stop reading.

Where SuiteQL saves units is when one query replaces three searches and a loop. That is a design win, not an API win.

Where SuiteQL genuinely wins

• Aggregation. SUM, COUNT, GROUP BY, HAVING. Let the database do the maths and hand you 12 rows instead of 80,000. This is the single biggest win available. A script that pulls 80,000 lines and totals them in JavaScript will always lose to one that just asks for the total.
• Real joins. Multiple hops, self joins, custom records joined to transactions joined to entities. Saved search joins are limited to one level out from the base record. SuiteQL is not.
• Narrow column pulls. Selecting id, tranid and trandate from a big table beats dragging a wide result set into script memory and ignoring most of it.
• Set logic. NOT EXISTS, IN with a subquery, UNION, CASE across joined tables. Doing that with two searches and a JavaScript filter is where scripts go to die.
• Anything a saved search cannot express. Date arithmetic, subqueries, deduplication, comparing a record against itself.

A typical example: open sales order value by subsidiary by month for the last 18 months. That is one SuiteQL, one round trip, roughly a hundred rows coming back. A saved search summary can produce the same figures, but the moment you need them inside a script and combined with something else, SuiteQL is both cleaner and quicker.

Where N/search still wins

• Single record reads. search.lookupFields costs 1 unit. Do not write SuiteQL for this.
• Reusing what the business already built. If a finance user maintains a saved search with 20 filters and the whole business trusts it, load it and run it. You inherit the logic and you do not own the maintenance.
• Very large volumes in Map/Reduce. Return a search object from getInputData and the framework streams it for you. It will happily chew through hundreds of thousands of records. query.runSuiteQL caps out at 5,000 rows, and that cap will catch you out.
• Filters that match how searches are indexed. Mainline transaction filters, date ranges on trandate, status filters. The search engine is good at these and you get the tuning for free.

The limits that actually decide it

These are the ones that turn into real support tickets.

• ResultSet.each() stops after 4,000 results. Quietly, with no error. If your totals are mysteriously wrong on large data sets, this is usually why.
• ResultSet.getRange() returns a maximum of 1,000 rows per call.
• query.runSuiteQL() caps at 5,000 rows. Use runSuiteQLPaged, or paginate inside the SQL.
• The REST SuiteQL endpoint pages at 1,000 rows using limit and offset.
• Deep offset paging slows down badly. If you are working through a large set, filter on the last internal id you saw rather than using an ever growing offset.

Know these before you benchmark anything. A script that silently truncates at 4,000 rows will look brilliantly fast right up until someone checks the numbers.

The thing that is actually slowing your script down

Nine times out of ten it is not the API.
It is the search inside the loop. Five hundred iterations, five hundred searches, 5,000 governance units gone and the script dies halfway through. Swapping those 500 searches for 500 SuiteQL calls fixes precisely nothing.The fix is to run one query up front, build a Map keyed on internal id, then do your lookups in memory inside the loop. That single change usually beats every other optimisation combined.

The other two common offenders:

• Pulling columns you never read. Every extra column is more data over the wire and more memory held in the script.
• No filter on the base record, so you pull everything and filter it in JavaScript. Filter in the query. Always.

Measure it, do not guess

This takes 20 minutes and settles the argument for good.

const start = Date.now();
const rows = query.runSuiteQL({ query: sql }).asMappedResults();log.audit('SuiteQL timing', {
    ms: Date.now() - start,
    rows: rows.length,
    unitsLeft: runtime.getCurrentScript().getRemainingUsage()
});

A few rules when you run it:

• Run it three or four times. The first run is cold and will look worse than it really is.
• Test against production data volume. A sandbox with 200 records tells you nothing useful.
• Test as the role that will actually run it. Permissions and subsidiary restrictions change the execution plan.
• Log the row count alongside the time. A fast result that quietly returned 4,000 rows instead of 12,000 is not a fast result.

SuiteQL traps worth knowing

If you are moving work over to SuiteQL, these catch nearly everyone at least once:

• transaction and transactionline are separate tables. Miss the join condition and you get a mess of duplicated rows.
• Mainline logic is manual. The saved search mainline filter does not exist here, so you handle it yourself in the WHERE clause.
• You get internal ids by default. Wrap the column in BUILTIN.DF() when you want the display value.
• Custom record tables are named after the record id, and custom fields sit as columns on the parent table using the field id.
• Be explicit with dates. Use TO_DATE with a format string rather than relying on implicit conversion.
• Permissions apply, but not always identically to a saved search. Test with the target role before you commit to a number.

A simple rule of thumb

• Reducing a lot of rows down to a few numbers? SuiteQL.
• Reading a handful of fields off one record? search.lookupFields.
• Streaming hundreds of thousands of records through Map/Reduce? Search object in getInputData.
• Reusing logic the business owns and maintains? Saved search.
• Complex joins or set logic? SuiteQL.
• Still not sure? Write both, time both, keep the winner.

Conclusion

SuiteQL is usually faster when you are aggregating, joining, or reducing a big data set down to a small answer. N/search is usually faster when you are reading one record, reusing an existing saved search, or streaming very large volumes through Map/Reduce.But the API choice is rarely the reason a script is slow. The query shape is. Move the work into the database, stop calling searches inside loops, ask only for the columns you need, and most of your performance problems disappear regardless of which module you picked.
Pick the tool that fits the question. Then measure it.