Has anybody seen that squiteql is actually faster ...
# suiteql
m
Has anybody seen that squiteql is actually faster than searches? I've been running tests and anytime it's a multiple paged result, searches have been faster through my tests. I've tried to optimize the parsing of the results as much as possible to return the results in a JSON format. The iterator that the netsuite help uses as an example is usually much slower. Below are the helper functions i'm running to run the query and parse the results
Copy code
lib.query = (options) => {
        return lib.parseSqlPagedResults((query.runSuiteQLPaged({
            query: options.sql,
            pageSize: options.pageSize || 5000,
        })));
    };
    lib.parseSqlPagedResults = (response) => {
        let results = [];
        response.pageRanges.forEach((page, pageIndex) => {
            response.fetch(pageIndex).data.results.forEach((rowObj) => {
                results.push(rowObj.asMap());
            });
        });
        return results;
    };
s
SuiteQL is supposed to be faster than traditional searches. I haven't done any side-by-side measurements myself, but vaguely recall that this was confirmed by NetSuite at one point?
m
yeah, they say that they are supposed to be faster but I haven't seen that in real time. Pretty much wondering if there is an optimized way to return the results to actually get it to be faster
s
so in one talk at suite world, they were saying that saved searches have been optimized to be fast. while SuiteQL is closer to the DB it doesn't have the optimization tools YET
m
interesting. everything i was hearing was that suiteql is supposed to already be much faster and to start moving over to them
s
yes it is the new way and will be optimzed to be faster then saved searche
m
Which talk was this? From my testing, I've generally seen speed improvements with SuiteQL
s
it was off the record but someone from netsuite said it
z
I had a large search that timed out in a scheduled script meaning it ran for over an hour without finishing. (making a packing slip for an 850 line SO with 3 different joins) I converted it to a query and it finishes reliably in under 30 seconds. I had a suitelet version and it could only handle 50-100 line sales orders but the query version can handle 400 ish lines reliably. I have a co-worker who has done some time testing in the browser console and the queries sometimes slower on small datasets. In that case, it is only a couple ms difference but as the data/columns increase, I've notice queries doing substantially better.
s
I think he was saying for large data set SuiteQl is way better but if there are many joins (smaller data set)it is not yet optimized
m
I see. I've only been testing on smaller data sets and adjusting page size to compare as if it was similar to a larger data size. @Zach Rumancik how are you parsing the data? are you running it similar to the asMap to get it in a usable json format?
s
I could be totally wrong about this so don't hold me to it lol
z
I typically use asMapped and even with that tagged on it goes considerably faster on larger searches or large quantities of data. You were right @Sim Greenbaum I am not great at being concise.
m
are you running the query.runSuiteQL()? Is there no limit on the amount of results it can return similar to search when you have to page?
z
Yes, I am running it like that and I just make a string query. I found the picture results I was referring to. Here is the timings for a small query. Even if the query is a little slower, it is nothing but consistent (ms).
Also this image was after about 200 other iterations and the search seemed to get faster like information was being cached so take what you want from it. If you plan on testing for yourself, load the modules in console and you can use performance.now() in a loop with the query or search to time it.
m
thanks! maybe they updated the function because when I originally looked at it, i thought it said it return a max of 5000 but it looks like it now has no limit. If the SuiteAnalytics Connect feature is enabled in your NetSuite account, there is no limit to the number of results this method can return. If the SuiteAnalytics Connect feature is not enabled, this method can return a maximum of 100,000 results. For more information about SuiteAnalytics Connect
z
I haven't had an application to hit the max but I just did a 'select * from transaction' and it only returned 5000 so I am not sure. I am still new to NS but in my short experience querying has been consistent when the speed is unnoticeable and faster when something takes time to load (if that made any sense).
m
it looks like there is a Query.run() which returns a maximum of 5000 and query.runSuiteQL(options) which is supposed to have no limit if the suiteanalytics connect feature is enabled
z
Hmm interesting. I may need to look into suiteanalytics if I ever need a large data set. Good luck with your endeavors and may the query ever be with you!