Google App Maker updating DB records in batches

Question

We are having a Google App Maker app using Google Cloud SQL tables. Our location is Prague, Central Europe and app.saveRecords() takes incredible 240ms at minimum. We have tried many things including changing the location of the instance but it did not solve the problem.

Fortunately, the solution is to insert new records in batches. This was brilliantly solved by the answer in my previous thread [ Google App Maker saving records to DB is taking 240ms per record ].

We would be so grateful if anyone provided us with a simple code to update records in the DB in batches [server-side scripting]. This way, we not only could insert completely new records in batches, but we could update the data of the records already saved in the DB fast way.

Let's say we have a DB with 3 custom fields:

product code | product availability | price

+ an example array with the data:

ourData[0] = ['pc001','in stock','99'];
ourData[1] = ['pc002','out of stock','49'];
ourData[2] = ['pc003','out of stock','199'];
ourData[3] = ['pc004','in stock','149'];
ourData[4] = ['pc005','in stock','299'];

...and we want to assign the availability and price data to the particular DB line using the key "product code". If the product code is not found in the DB, then we insert a new record to the DB.

Multiple single line inserts/updates take Google App Maker users in the EU too long because the query does go to the US even if the SQL instance is located in the EU. Running this in batches could break today's Google App Maker limitations outside the US. Thank you so much for any help to make Google App Maker a great tool for beginners all around the world.

Answer 1

With the help of the others here in the thread, I managed to reduce the time to insert or update a record to 50ms. Furthermore, I have tested out to insert/update 10000 records to a table containing 60 fields. In this case, the speed was 60ms per record. Hereby, I am providing anyone (who may find it useful) with my final code. Thank you for all the helpful answers and comments!

/* We use Google Cloud SQL Table "testDB" that contains these fields:
   - Id (the default primary key for DBs in Google App Maker)
   - productCode
   - availability
   - price

   We have an array called "data" which contains the data [productCode, availability, price] we want to save to the database.
*/

function saveDataNew(){
    var data = [];
    data[0] = ['pc1','in stock','99'];
    data[1] = ['pc2','out of stock','129'];
    data[2] = ['pc3','in stock','78'];
    data[3] = ['pc4','in stock','95'];

    //load all the records in the DB (no filter is used)
    var query = app.models.testDB.newQuery();
    var records = query.run();
    console.log("Found " + records.length + " records in the database.");

    //create an array to help us find DB's Id for each unique productCode.
    var helpingArray = [];
    for (var x in records) {
      var product = records[x]; //get each record in the DB
      helpingArray.push([product.productCode,product.Id]); //save this record's productCode and Id into the helping array
    }

    //setting up the writing in batches
    var totalRecords = data.length;
    var batchLimit = 500; //size of a batch round - number of lines for each app.saveRecords();
    var roundlimit;
    if(totalRecords < batchLimit){
      roundlimit = totalRecords;
    }
    else{
      roundlimit = batchLimit;     
    }
    var totalRounds = Math.ceil(totalRecords / batchLimit);
    var round = 1;
    var currentItem = 0;

    //start writing in batches
    do{ 
        var recordsToSave = [];
        //create or update a record in the DB for each line of our data within one batch round
        for(var i=currentItem; i<roundlimit; i++){
          var wantedCode = data[i][0]; //get the productCode of the current line of the data array
          var orderNum = -1; //create a variable to find the order number of the productCode in the helping array
          for(var z=0; z<helpingArray.length; z++){ //search for the productCode in the helping array
            if(helpingArray[z][0]==wantedCode){
              orderNum = z; //save the line's number if the productCode is found in the helpingArray
              continue;
            }
          }
          var productRecord;
          if (orderNum == -1){ //there was no line with the productCode found the helpingArray => create a new record
                productRecord = app.models.testDB.newRecord();
                productRecord.productCode = data[i][0]; 
          }
          else{ //the productCode was found in the helpingArray => edit the existing record in the DB 
                productRecord = records[orderNum];
          }
          //provide the record with the data
          productRecord.availability = data[i][1];
          productRecord.price = data[i][2];

          //cumulate records and save them once the batch round is finished
          recordsToSave.push(productRecord); 
        }

        //a batch round has been finished, save records if there are any
        if(recordsToSave.length){
            console.log("Records saved: "+recordsToSave.length);
            app.saveRecords(recordsToSave);
        }

        currentItem += batchLimit;
        round++; 

        if (totalRecords < round*batchLimit){
          roundlimit = totalRecords;  
        }
        else{
          roundlimit += batchLimit;    
        }
    } while(round <= totalRounds);
}

Answer 2

This issue can be solved by using the proper server scripting and I strongly encourage you to read how server scripting works in the official documentation. Pay close attention at the Querying records example.

So, taking as base the previous solution example, it can be slightly modified to achieve your needs. This is how it should look:

function saveData(){

    //get the data
    var data = getData();

    var totalRecords = data.length;
    var batchLimit = 2000;
    var totalRounds = Math.ceil(totalRecords / batchLimit);
    var round = 1;
    var roundlimit = batchLimit;
    var currentItem = 0;

    do{
        var recordsToSave = [];

        for(var i=currentItem; i<roundlimit; i++){

            var recordData = data[i];
            var productCode = recordData[0];

            //check if the record already exists
            var query = app.models.testDB.newQuery();
            query.filters.productCode._equals = productCode;
            var productRecord = query.run()[0];

            //if non existent, create a new one
            if(!productRecord){
                productRecord = app.models.testDB.newRecord();
                productRecord.productCode = productCode;
            }

            productRecord.availability = recordData[1];
            productRecord.price = recordData[2];
            recordsToSave.push(newProduct);
        }

        if(recordsToSave.length){
            app.saveRecords(recordsToSave);
        }

        currentItem += batchLimit;
        roundlimit += batchLimit;
        round++;

    } while(round <= totalRounds);

}