bad performance loop

[Marcos Gomes]

I have a performance problem and know that using ES6 is possible to reach the same result more efficiently.

How can I use Filter / Map / Reduce in this example?

function seek(){

  

  var arr1 = [0,1,2,3,4,5];

  var arr2 = [[0,'a','b','x'],[3,'a','b','xx'],[5,'a','b','xxx']];

  var arr3 = [];

  

  for(var i = 0 ; i < arr1.length ; i++){

    var x = arr1[i];

    for(var j = 0 ; j < arr2.length ; j++){

      if(arr1[i] == arr2[j][0]){

        arr3.push([arr2[j][3]]);

      }else{

        arr3.push([]);

      }

    }

  }

  Logger.log(arr3) //[[x], [], [], [], [], [], [], [], [], [], [xx], [], [], [], [], [], [], [xxx]]

}

In the real case, I am searching in an array of 200 rows and another with 6000 rows. Using for loop, does not work.

[Steven Bazyl]

Arrays in apps script have .forEach(), .filter(), .reduce(), and .map() functions. However, using those in place of for loops won't make a difference in terms of how fast this runs. You need to take a different approach to reduce the complexity (how many operations are being performed). As written, the inner most part of that code would run 1,200,000 times given the size of the data you described.

It's also really hard to suggest an alternative approach without knowing what the problem is you're trying to solve. Does your output require the empty arrays for things you don't find? Is there meaning to the order of the output? And if there is, could that meaning be expressed differently? If you could restate what you're trying to do in simple terms that would help a lot.

For example, if the problem were "Assuming arr2 is an array of rows, return all the values in the last column where the value of the first column is in the set arr1" then you could do something like:

function seek(arr1, arr2) {
  // Convert arr1 to a map keyed by value for fast lookup
  var keys = arr1.reduce(function(obj, value) {
    obj[value] = true;  
    return obj;
  }, {})

  return arr2.reduce(function(foundValues, row, index) {
    if (keys[row[0]] !== undefined) {
      foundValues.push(row[3]);
    }
    return foundValues;
  }, []);          
}

If you need to know *which* row in arr2 contained the value, then instead of pushing just the value, push an array or object like:

foundValues.push({index: index, value: row[3]});

I did a quick test and with that size data an approach like this is still fast. Anyway, can't help without knowing more about the problem, but hope that gets you on a better track :)

 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/fd7e822f-129f-4207-89e1-8f4c445d6971%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marcos Gomes]

Thank you Steven Bazyl,
Link to spreadsheet with the actual problem: https://docs.google.com/spreadsheets/d/1rmnumw-aMJkPAWa0JmgjaQ8n-2ebaFjjJXGhbiiCG6M/edit?usp=sharing

In the "DB" sheet column A is the code to be searched, column D is the result that must be taken to the "preCalc" sheet in column R, as found in column D (COD_MESO).
Loop is not the best solution, probably using ES6 with a more efficient algorithm I believe is possible.
I do not know how to search array inside array 2D and return the result using map or filter.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADJ_TyZDbxZ6a66eW7oU9h69dgSiZYSKQ%2BDyW5hMO%2BT29HF4hw%40mail.gmail.com.

[Andrew Roberts]

Here's an article you might be interested in on the relative speeds of various array processing methods:

https://tanaikech.github.io/2018/04/16/benchmark-loop-for-array-processing-using-google-apps-script/

[Steve Webster]

I like binary search.

https://stackoverflow.com/questions/29345356/google-apps-script-fastest-way-to-find-a-row?rq=1

Kind Regards,

Steve Webster

SW gApps LLC, President 

Google Product Expert in: Google Apps Script, Drive, and Docs 

Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 

Add-ons: Text gBlaster and Remove Blank Rows

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4d12ec77-6c4c-433c-8a33-7b54f7e72129%40googlegroups.com.

[Marcos Gomes]

That's right, Andrew! Tanaike did a great job.

It is possible to search in a fractional way, but it is not efficient and consumes a lot of computational resources.  
Unfortunately I can not find an example of how to use filter/map/reduce to interact array with array (multidimensional), this is the point.

Excuse me for bad english. 

Thanks!

--

[Marcos Gomes]

Thanks, Steve
I read some articles and actually searching in JSON is the best way, but I still do not find an example of how to search array with array using a more efficient technique. I tried several ideas and none worked.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BV5gQ75NaXkDw1u3zwf2ef1cq%2B539B402G2z1XHRTdSiDr0Tw%40mail.gmail.com.

[Bruce Mcpherson]

here's something close to what you need that you can adapt to present the results as required

(() => {

const arr1 = [0,1,2,3,4,5];

const arr2 = [[0,'a','b','x'],[3,'a','b',0],[6,'a','b','xxx']];

// lets asssume you want to find any values in arr2 which appear in arr1

const matches = arr2

.map(f => arr1.map(g => f.indexOf(g)).filter(g => g !==-1 ))

.map((f, row) => f.map(g => arr2[row][g]));

console.log(matches);

})();

result

[ [ 0 ], [ 0, 3 ], [] ]

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CANA%3DMHtMGkcXvn-ihHbaO4dUbrmcoYdRu1eW8bxs2frcSuX43w%40mail.gmail.com.

[Marcos Gomes]

blew my mind!   :D

Master of masters. Thank you great Bruce Mcpherson.
I was unable to implement using Google Script.
The functions with arrows are still unknown for me.
(() => {
  //code
})();

PS: Two lines of code and it will take months to understand :)

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CANijH0W3CxH_7b07BnJTwjZQR%3DWJ8vbbiCpTAhAJzKLfsv%3DH3A%40mail.gmail.com.

[Bruce Mcpherson]

in es6

(a) => b

is (very nearly) equivalent to function(a) { return b; }

so here's the thing in pre es6, with a few comments to help you understand whats going on.

btw - filter/map/reduce etc is about cleanliness more than perfomance.

Another approach would be to convert the values in arr1 into keys, but personally I prefer this straight mapping method.

(function () {

const arr1 = [0,1,2,3,4,5];

const arr2 = [[0,'a','b','x'],[3,'a','b',0],[6,'a','b','xxx']];

// lets asssume you want to find any values in arr2 which appear in arr1

// we want the final result to be the same shape as arr2, so map that

const matches = arr2

.map(function(f) {

return arr1.map(function(g) {

// if anything from arr1 is in this row, remember its position

return f.indexOf(g);

})

.filter(function(g) {

// and get rid of any that there were no matches for

return g !==-1;

});

})

.map(function(f, row) {

// now each row contains an array of indices of matching items

return f.map(function(g) {

// so pluck then out

return arr2[row][g];

});

});

})();

You might also find these posts useful

http://ramblings.mcpher.com/Home/excelquirks/gassnips/fiddler http://ramblings.mcpher.com/Home/excelquirks/gassnips/es6shim http://ramblings.mcpher.com/Home/excelquirks/gassnips/es6arrow http://ramblings.mcpher.com/Home/excelquirks/gassnips/constsandscopes

http://ramblings.mcpher.com/Home/excelquirks/gooscript/arrayfunctions http://ramblings.mcpher.com/Home/excelquirks/gassnips/composer .. and lots more like that if you fish around

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CANA%3DMHsDWUgGoGcc0%2BswESzzLDW39gQa7e8RSEC3eOJgZ%2BaKug%40mail.gmail.com.

[Bruce Mcpherson]

btw .. the object method is faster (assuming you dont need to retain row numbers), but in any case, I doubt if this is measurable anyway. Here's a test with 10,000 rows of 100 items of both methods against a reference list of 500 items - each under a second

(() => {

const filler = (count, high, low = 0) =>

Array(count).fill(0).map(f => Math.floor(Math.random()*(1 + high - low) + low));

const s1 = new Date().getTime();

// generate some random stuff

const arr1 = filler (500, 50);

const arr2 = filler(10000,0).map(f=>filler(100, 500));

// lets asssume you want to find any values in arr2 which appear in arr1

const arro = arr1.reduce((p, c, i) => {

p[c] = true;

return p;

},{});

const matches = arr2.map(f => f.filter(g => arro[g]));

console.log(' object method', new Date().getTime() - s1);

const s2 = new Date().getTime();

const m2 = arr2

.map(f => arr1.map(g => f.indexOf(g)).filter(g => g !==-1 ))

.map((f, row) => f.map(g => arr2[row][g]));

console.log(' map method', new Date().getTime() - s2);

})(); 

 object method 119
 map method 994

[Marcos Gomes]

Thanks!!!!

Awesome work!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CANijH0VryrJsL__DprFG4Or-wK5e4sghRYVUNE7SCPW3j%3DUxkg%40mail.gmail.com.