Hi! We have a couple annoying situations that we're dealing with. If we want to get any single row from NS, is there a preferred way from a performance POV? Ex:

select top 1 c.id from customer c

select c.id from customer c where rownum <= 1

is one faster than the other? Some of our clients the query will timeout. The situation is we're trying to detect if custom fields exist on an object and from what we've gathered, we need NetSuite to actually return 1 row so it'll process the proper validations... as in, we can't do

select c.bad_field from customer c where 1 = 2

it'll return an empty set and not error out. If we do

select top 1 c.bad_field from customer c

it'll error out, but for some of our clients, this times out.

There is a

customfield

table you can query

Hah, I should have known someone was going to suggest that option 🙃 It's certainly an option we could work into how we do our validation. I as going to type a big long answer about how we do it today and why using the

customfield

table would be a pain, but I think overall rearchitecting things to use that table is probably best. Do you know if there's a way to get the list of all built-in columns for a given NS table?

In SuiteQL both

TOP 1

and

WHERE ROWNUM <= 1

push through the same engine, so there isn’t a real performance difference. The timeouts you’re seeing usually come from the query planner scanning too much data before it applies the limit. If your goal is just to trigger validation on a column, the best practice is to use

SELECT TOP 1

with a very selective condition like

WHERE isinactive = 'F'

or any indexed field so SuiteQL can short-circuit faster. That way you still force NetSuite to evaluate the field but reduce the chance of a full table scan that causes timeouts.

@Pandorahiccup Thanks, that was our loose understanding how things are being processed under the hood. Unfortunately our clients vary from brand new clients with very little historical data or huge multinational companies that are pushing the limits of NetSuite and we have struggled to have a good selective condition that'll work across the board. I appreciate both you taking the time to discuss!