N/query versus N/search: choosing the right SuiteScript data access module
netsuite N/query vs N/search which is faster
Also searched as
- when to use N/query instead of N/search in suitescript
- netsuite suiteql N/query performance
- N/query runSuiteQL vs search.create governance
- netsuite N/query pagination best practice
Short answer
N/query runs SuiteQL directly against NetSuite's relational schema and is generally cheaper and faster for joins, aggregation and large filtered pulls, while N/search wraps the same saved-search engine used by the UI and is better when you need formula columns, summary types or a saved search object you can reuse. Both consume governance units per page fetched, but N/query's ability to express a join in one query often beats the multiple round trips a search-based join pattern requires.
Applies to: SuiteScript 2.1 (N/query available from 2.1), N/search available in 2.0 and 2.1, all NetSuite editions
Pick between N/query and N/search for a given script
- 1Default to N/query.runSuiteQL when the task is a join across multiple record types, an aggregation (COUNT, SUM, GROUP BY), or a bulk read that a saved search would need several linked searches to express.
- 2Default to N/search when you need a formula field, a summary search type, or want to reuse an existing saved search object (search.load) that business users already maintain through the UI.
- 3For either module, page results instead of pulling everything at once: N/search with search.run().getRange() or a PagedData object via .runPaged(), N/query with an explicit OFFSET/FETCH or a bookmarked WHERE clause on an indexed column.
- 4Measure with runtime.getCurrentScript().getRemainingUsage() around each call in a test deployment before committing to one approach; a single N/query call is usually a small flat governance cost regardless of row count fetched per page, while N/search cost scales more with the number of columns and joins in the search definition.
- 5Avoid record.load in a loop when either N/query or N/search can return the needed fields directly; loading full records is the single biggest avoidable governance and latency cost in NetSuite scripts.
- 6For sublist-heavy questions (for example, all lines across many transactions), prefer SuiteQL against transaction/transactionline with an explicit join over iterating record.load and sublist line counts.
- 7Cache static or slow-changing reference data (item lists, customer segments) once per script execution using a module-level variable rather than re-querying inside a per-record loop.
- 8When in doubt on very large datasets (tens of thousands of rows), lean toward N/query: its result set handling and simpler per-call governance model tend to scale more predictably than deeply joined saved searches.
What each module is actually built on
N/search is a SuiteScript wrapper around NetSuite's saved search engine, the same engine behind Lists > Search > Saved Searches in the UI. It supports search filters, summary types (group, sum, count, average) and formula fields (formulatext, formuladate, formulanumeric) using NetSuite's own expression syntax, and a search definition can be saved and reused by both scripts and end users.
N/query (added in SuiteScript 2.1) executes SuiteQL, a SQL-like query language against NetSuite's relational schema directly, supporting standard SELECT, JOIN, WHERE, GROUP BY and window functions in many cases. It does not go through the saved search engine at all, so it does not inherit saved search's formula syntax or summary type conventions; you write real SQL instead.
Because they are different engines, a query that is awkward in one is often natural in the other. Multi-table joins with aggregation are usually simpler and cheaper to express in SuiteQL; a formula field driven by a saved search's own expression language, or a search a non-developer needs to maintain, favors N/search.
define(['N/query'], function(query) {
function onRequest(context) {
var results = query.runSuiteQL({
query: "SELECT t.tranid, SUM(tl.netamount) AS total FROM transaction t JOIN transactionline tl ON tl.transaction = t.id WHERE t.trandate >= ? GROUP BY t.tranid",
params: ['1/1/2026']
}).asMappedResults();
}
return { onRequest: onRequest };
});Governance cost in practice
A search.create().run().getRange() call typically costs a small, fairly flat number of units per page regardless of how many columns are returned, but a search with several joined record types or heavy formula columns can push the underlying execution time up even if the governance unit charge looks similar on paper, which shows up as slower wall-clock time rather than a governance error.
N/query.runSuiteQL also charges a flat-ish per-call cost, but because one SuiteQL statement can replace what would otherwise be several chained searches (for example, a search on transaction joined conceptually to a separate search on item), the total governance spent across a script run is often lower with N/query for genuinely relational questions.
Neither module eliminates the need to page: both N/search (1,000 results max per unpaginated search.run().getRange(), higher with runPaged) and N/query (default row limits per call) require explicit pagination logic for datasets beyond a few thousand rows.
Capability gaps to plan around
N/query cannot directly reuse a saved search object the way search.load(id) can; if the business already maintains a saved search with specific filters non-developers control, N/search is the natural fit even if SuiteQL could technically express the same logic.
Summary search types (group by with running totals, certain matrix summaries) map cleanly onto N/search's summary type API but need to be hand-rolled as GROUP BY and aggregate functions in SuiteQL, which is straightforward for standard aggregates but more work for anything NetSuite's search engine does with specialized summary logic.
SuiteQL is read-only; any write path still needs record.submitFields, record.save or the REST record API. Neither module differs on this since neither N/search nor N/query supports writes.
Common pitfalls
- !Rewriting every existing saved-search-based script into SuiteQL without checking whether the saved search is also maintained by business users through the UI, breaking that workflow.
- !Assuming SuiteQL joins are always cheaper without testing; a badly filtered join across very large tables (transactionline especially) can still be slow even if the governance unit cost looks small.
- !Forgetting that N/query results need explicit type handling (asMappedResults() vs iterator) and that column names in the mapped result match the SQL alias, not the NetSuite field ID convention used by N/search.
- !Not paginating N/query results and silently truncating a large export at the default row limit.
- !Mixing formula-heavy N/search definitions into a hot code path (a User Event triggered on every save) instead of moving that logic to a scheduled or batch process.
How an ERP-grounded AI assistant handles this
ERPray grounded on a customer's SuiteScript codebase can look at an existing search-based script and suggest, with a concrete before/after, where a SuiteQL join would replace several chained searches or record.load calls, along with the governance and latency tradeoff in plain terms. For teams unsure which module fits a new requirement, that grounded comparison is faster than prototyping both approaches by hand.
Frequently asked questions
Is N/query available in SuiteScript 2.0?
No, N/query requires SuiteScript 2.1. Scripts still on 2.0 need to use N/search, or be upgraded to 2.1 to gain access to runSuiteQL and the rest of the query module.
Can N/query replace a saved search that end users also run from the UI?
Not directly as the same object; a saved search visible in the UI is created and run through N/search (search.load or search.create). SuiteQL is a script-only, code-based query and does not produce a UI-visible saved search asset.
Does N/query support formula fields like N/search does?
Not in the same syntax. SuiteQL lets you compute expressions directly in the SELECT clause using standard SQL functions, which covers most of what saved search formula fields do, but the expression language and available functions differ from NetSuite's formulatext/formulanumeric syntax.
Which is better for a nightly export of hundreds of thousands of rows?
N/query is usually the better fit for very large, mostly tabular exports because a single well-indexed SuiteQL query with proper pagination tends to be more predictable than a deeply joined saved search at that scale, though both still require careful paging logic either way.
Related
Avoiding governance limit errors in NetSuite Map/Reduce scripts
A NetSuite Map/Reduce script gives each map or reduce invocation of a key its own fresh governance allotment instead of sharing one pool across the whole run, so most SSS_USAGE_LIMIT_EXCEEDED failures come from a single key doing too much work, not from the total record count. Fix it by moving heavy logic out of getInputData, keeping map and reduce functions idempotent and cheap per key, and checking runtime.getCurrentScript().getRemainingUsage() before expensive calls.
AdvancedExporting large volumes of data out of NetSuite reliably
For large NetSuite exports, the right tool depends on frequency and volume: SuiteAnalytics Connect (ODBC/JDBC) suits ad hoc and scheduled BI pulls with SQL filtering, SuiteQL over SuiteTalk REST or N/query suits programmatic incremental syncs, and a Map/Reduce script suits transformations that must happen inside NetSuite before export. Whichever method is used, filtering by lastmodifieddate for incremental pulls instead of re-exporting the full dataset every run is what actually makes large exports sustainable.
AdvancedHow NetSuite scheduled script queueing actually works
A Scheduled Script deployment queued behind other jobs is not failing; NetSuite runs a limited number of scheduled and Map/Reduce script queues concurrently per account, so deployments wait their turn based on account concurrency and deployment priority. The practical fixes are staggering trigger times, setting deployment priority correctly, and designing scripts that reschedule themselves cleanly with N/task instead of running one long unbroken execution.
AdvancedWorking with NetSuite's SuiteTalk REST Web Services API
SuiteTalk REST exposes NetSuite records at https://ACCOUNTID.suitetalk.api.netsuite.com/services/rest/record/v1/{recordType}/{id} and ad hoc SQL-like queries at /services/rest/query/v1/suiteql, both authenticated with TBA or OAuth 2.0. It returns JSON, paginates large result sets with limit/offset and a hasMore flag, and needs expandSubResources or a specific fields query to pull sublist and related data efficiently.
Error fixFix NetSuite SSS_USAGE_LIMIT_EXCEEDED Error
SSS_USAGE_LIMIT_EXCEEDED fires when a SuiteScript execution consumes all the governance units (usage points) allotted to its script type before it finishes. Fix it by checking runtime.getCurrentScript().getRemainingUsage() before expensive calls, yielding or rescheduling in Scheduled scripts, and moving heavy record-count work into Map/Reduce, which yields automatically across stages.
Error fixFix NetSuite INVALID_FLD_VALUE Error
INVALID_FLD_VALUE means NetSuite rejected a value you tried to set on a field because it does not match the field's expected type, list option, or reference record. Fix it by confirming the internal ID or text value actually exists on that field's source list and matches the field's value type (text versus list versus record reference) before setting it.
AI for ERPAI for NetSuite, Beyond the Built-In Text Tools
NetSuite's built-in AI covers text generation, not grounded answers on your own data. See how a private LLM over SuiteQL adds real Q&A and controls.
Stuck on Oracle NetSuite?
Talk to engineers who work inside Oracle NetSuite every week, and who build private AI that answers these questions from your own ERP data.