https://netsuiteprofessionals.com logo
Join Slack
Powered by
Hi all, I am trying to use suiteQL. we used to use...
# suitescript
v
Hi all, I am trying to use suiteQL. we used to use restlet with 
savedSearch.runPaged({pageSize}) and pageRangees.forEach
However, due to the savedSearch calculating the fields upon request is made, it is timing out when there is more than 20k records… and I have heard that suiteQL and runSuiteQLPaged have a better performance.
Copy code
var results = query.runSuiteQLPaged({
    query: 'SELECT customer.entityid, customer.email FROM customer',
    pageSize: 10
});
in the docs, it is using 
customer
as a table name. If I want to query a savedSearch, what should the table name be? I tried with the savedSearch id but my restlet does understand what savedSearch I am trying to query.
s
Look under Setup -> Records Catalog for the SuiteQL schema.
s
You cannot magically use a saved search and run the search as query instead, which is what it sounds like you are doing. You would need to build the query separately
v
@Sandii ahh, that is what I was missing, Thank you very much. I will look into that now.
s
Just be aware that switching to SuiteQL might give you a speed bump, but it won’t fix an inefficient search. I think much of the speed benefits with SuiteQL are that there is less conversion to do, and because you are creating the query yourself, you can optimize it slightly. The queries generated by saved searches are probably not well-optimized. But, if there is something slowing down the saved search, then the equivalent SuiteQL will suffer from the same performance hits. Some of the best ways to improve speed are to remove unnecessary joins, formulas & functions and try to return raw data, and manipulate it in your SuiteScript code instead, and eliminate unneeded large text field as they can drag down performance as well. This applies to both SuiteQL and saved searches. A restlet can only run for 300 seconds before it times out. I try to keep any restlet to under 30 seconds if at all possible. If not, then you increasingly run the risk of timeout or a really bad user experience while waiting for the response.
💯 1
keanu thanks 1
v
@scottvonduhn Thank you so much for the detailed explanation. the savedSearch is calculating some of the fields when restlet make a request to it. like I saw that it was using SUM function. I believe that is why it is slowing down and time-out eventually. I do not have privilege to savedSearch. but I would consider getting raw data and calculate in the script. I did not think about that.
s
Doing grouped queries to produce a SUM shouldn’t be too inefficient, in fact if you are returning a lot less data, that can even be faster. Without knowing the details, it’s hard to say why a particular search or query may be problematic. I usually try to start with the smallest working query or search, and slowly build up from there. Often time, you can identify what is causing problems when you add in fields and filters one at a time.
v
@scottvonduhn Thank you. I see. I am also looking into promise. For some reason, 
pageData.fetch.promise({index: pageIndex}).then(res => res).catch(err => err)
does not execute anything and returns null… but at least i got more understanding of suiteQL. 🙂
s
unless you are able to execute two or more promise calls in parallel, it likely will not improve anything for you, as you still need to wait for the results.
👀 1
Does the N/query pagedData.fetch even have a .promise version? I don’t see it in SuiteAnswers, though admittedly I am having trouble searching now in the new SA interface. I see that the N/search PagedData.fetch does have a .promise version, but these are two different PagedData objects, from different modules, and may not work the same.
v
oh actually what I am trying is 
N/search PagedData.fetch
. but it does not work… the program execution does not go into then nor catch clause… https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_4491674556.html
But I also tried like
Copy code
define(['N/log'],
    (log) => {
        const request = () => {
            return new Promise((resolve) =>
                setTimeout(() => resolve({status: 'done'}), 2000)
            )
        }
        const get = (event) => {
            request()
                .then(r => {
                    console.log("@@ resolved", r)
                    return r;
                })
                .catch(err => {
                    console.log("@@ catch", err)
                })
            
            return 123;
        };

        return {
            get
        };
    }
);
I see that suiteScript 2.1 has native javascript Promise. I ran it locally and worked. But on the netsuite, it does not throw an error. And ignores my promise function and then/catch…. Do you have any idea?
s
How many total results do you expect to get back? Can you even run this saved search form the UI? How long does it take from the UI?
I would take a search that I know works in the UI, then try running that in the code.
I see you were trying to use setTimeout in the code example above. That doesn’t work in NetSuite server scripts, like restlets.
It will work locally or in a client script, but not server-side
v
actually i do not get any results…. when i just used 
pageData.fetch()
it times out. there is not a lot of records in the savedSearch. but the savedSearch structure is a bit complex. when I used 
pageData.fetch.promise()
to get data from savedSearch, it returns absolutely nothing… the program just ignores the promise.
And I tried running without setTimeout but still the promise is ignored…
s
okay, but can you run the saved search through the UI? if so, how long does it take?
I would ignore everything else for now, until you know if the search itself is the issue, and the best way to determine that is to run it manually and see what it does and how long it takes.
v
when you say run the savedSearch through UI, like going to savedSearch page and run it? then no, i do not have permission to go to the page. if you meant like running the restlet on UI that gets data from savedSearch then no. it timesout
s
I would ask whomever administers the account to give you saved search access. It is difficult to develop when you can’t verify through the UI what data the script is supposed to be processing.
v
Understood. I would do that.
s
Also, if your search would return less than 4,000 results, you don’t need to use paging, you can just run the search directly. Are you expecting 100 results, 1,000 ? what’s the expected size?
v
at most 30k
s
oh, then yes, you do need it. And i would not consider that a small number of records for a restlet to return.
v
i was expecting 20k but there is an increase and it started timingout
s
that is a lot of data for a single restlet call
v
I see. Thank you very much. I am new to restlet and that was something i did not consider. I would consider about map/reduce as well
s
well, if you are using a restlet, then I presume this is for an integration? you can’t use a map/reduce for an integration call
🤯 1
at least, not directly
v
Yes. It would not be directly. But Thank you for the advice!!
s
There may be other options, though, like running a search or query through REST web services.
👀 1
You could have the client calling the restlet give a call back URL, then have a map/reduce run and post the final results to that URL
v
that sounds great. I would absolutely try that out. I havent thought about that way of using restlet
21 Views
Open in Slack
PreviousNext
Powered by