Anyone have issues with queries that use ROWNUM?

The REST API and

query.runSuiteQLPaged

might have some bugs depending on the order of the columns. They don't happen with

query.runSuiteQL

You can try with SuiteQL Query Tool with the pagination option unchecked and see if you get the same issue.

My issue wasn't with the rownum stuff in the end. I did some other optimizations instead. for instance,

AND (lower(joinedList.scriptid) = 'to_be_processed' OR lower(joinedList.scriptid) = 'finished')

was twice as fast as

AND lower(joinedList.scriptid) in('to_be_processed', 'finished')

This might be faster:

AND regexp_like(joinedList.scriptid, '^(to_be_processed|finished)$', 'i')

https://timdietrich.me/blog/netsuite-suiteql-regexp-like-keyword-searches/

NetSuite is full of surprises but I wouldn't have expected a regex search to be faster than an IN clause

They could be implemented using different algorithms, and one may be more efficient for the specific search terms and the data being searched. I always have to remind myself that an algorithm that performs worse on average may be the fastest for a particular situation.