I have a search which I am running in my code which I call from a Restlet. It takes more than a minute to run the search. I created the same exact search in the UI (via that same code that I am using to run the search) when I run the same search in the UI it takes about 10 seconds. Why would there be a discrepancy?

how many results does the search return?

indexing, are you building the search from scratch in the code or loading/running the same search

It has 16,465 results

Are you getting all 16k or just the first 1k?

All

The search is fast in the UI because it only grabs the first 25 or 50 or whatever your initial page setting is

Ahhhhh!

That's not to say there's nothing you can do to improve it, depending on what you're doing after or if you have any complex formulas and such

No complex formulas. Just basically all sales order lines where the ship date is for the next week.

Ah, are you computing the averages after the fact, or letting the search do it?

Computing after the fact. But I put console timers all over the code and the reducing I am doing after takes about a second. It is getting the search results that really eating the time

could you let the search do the averaging for you?

Need other columns

Are they summarized as well?

16k sales order lines

nvm you said you're reducing

Yep

The only way I can see would be to reduce the number of results, even if that meant splitting them into multiple searches, but without having a whole lot more context/info, it's hard for me to prescribe that course of action. If you need all 16k no matter what, you're probably stuck

Would query module be a better/faster choice?

I would let some query experts like @tdietrich speak to that, but in my highly unscientific, anecdotal testing in browser console, there's no significant difference in performance between the two

I concur with Eric regarding search speed

@stalbert Yeah that ain't gonna work in this use case

Depending on how real-time you need this, you could also do all the processing on a schedule and save the results on a custom record. Then have your restlet access that infomration very quickly. I do similar for inventory turnover. Process on schedule and save to JSON, then have portlet access the JSON object in the filecabinet.

That's an awesome idea @jarens! I'll run that idea by my boss. Thanks!

although i wouldnt expect 16000 lines to be a large enough data set to matter, you may be better off doing a search to get all the internal ids

@erictgrubaugh Just thinking about it some more. How does the UI search know how many results there are if it is only getting the first 25/50 results? Doesn't knowing the result count mean it ran the query?

No

@erictgrubaugh regarding SuiteQL vs Saved Search performance, I just watched a talk from NetSuite PM who claimed it's 100% faster. My own testing with a specific search was ~50 secs using SuiteQL and ~1m20s as a saved search.

I've also noticed (qualitatively) that SuiteQL seems to execute fast compared to traditional saved searches

To be fair, my only anecdotal testing was with extremely basic search/query comparison: no joins, no formulas, very small Column set

I actually am no fan of SQL, but on the topic of joins - SuiteQL is the killer app for NS search compared to the competition. I needed to do an ad-hoc join and it is supported by SuiteQL. As far as I know you cannot do ad-hoc joins between arbitrary tables/columns with any other NS scripting technique?

https://netsuiteprofessionals.slack.com/archives/C29HQS63G/p1603397756321000 @erictgrubaugh I rethought it, and as you suggested, I was able to do it in a formula and a sum instead of getting all results and then computing/reducing myself. Reduced the time from close to 2 minutes to about 7 seconds!

I somehow missed this thread earlier, but I do want to add a few things... My experience has been that SuiteQL is generally faster than using searches, both in terms of development time and execution time. And the more complicated the query / search, the better SuiteQL seems to be. (It would be interesting to take a complicated search, create the SuiteQL equivalent, and compare the response times.) Also, SuiteTalk REST was my intro to SuiteQL. I was originally using that combination for my integration work. However, I've since found that using RESTlets with SuiteQL is generally faster than using SuiteTalk REST. I think that has to do with the overhead that SuiteTalk has (parsing the request, preparing the response, etc).

my main problem so far with SuiteQL is no schema and hence IDE support. Just opaque SQL strings in code.

The Records Catalog (available starting in 2020.2) can be helpful - and it's customized to the instance that you're in. But if you're looking for something visual, that won't help.

Aye,I've seen that new records catalog (though I see it often breaks) - but what I really mean is to accept SQL as a serious language I expect full featured IDE support for it. I don't get that in Webstorm. Notably, IDEA (and if I recall correctly, full VStudio?) do support that. Basically i the new 'records catalog' thingie could be downloaded as DDL we'd be golden, no?

FWIW you can add the (paid) DataGrip plugin to WebStorm as well: https://plugins.jetbrains.com/plugin/10925-database-tools-and-sql/pricing

Yes, I've played with DataGrip in the past

Probably

but at least that could get you good SQL syntax support

aye

there was also an open source DB Navigator plugin, but I fear it didn't work with Webstorm for some reason last time I tried it