Let's say I have a a script that iterates over a list of 400 objects. Each object has anywhere from 1 to 10 properties. Each property is a reasonable size string or a somewhat large integer.
Is there a significant difference in performance of saving these objects into ScriptDB vs saving them into Spreadsheet(w/o doing it in one bulk operation).
Yes, there is a significant difference! Huge! And I have to admit that this experiment didn't turn out the way I expected.
With this amount of data, writing to a spreadsheet was always much faster than using ScriptDB.
These experiments support the assertions regarding bulk operations in the Google Apps Script Best Practices. Saving data in a spreadsheet using a single setValues() call was 75% faster than line-by-line, and two orders of magnitude faster than cell-by-cell.
On the other hand, recommendations to use Spreadsheet.flush() should be considered carefully, due to the performance impact. In these experiments, a single write of a 4000-cell spreadsheet took less than 50ms, and adding a call to flush() increased that to 610ms - still less than a second, but an order of magnitude tax seems ludicrous. Calling flush() for each of the 400 rows in the sample spreadsheet made the operation take almost 12 seconds, when it took just 164 ms without it. If you've been experiencing Exceeded maximum execution time errors, you may benefit from both optimizing your code AND removing calls to flush().
All timings were derived following the technique described in How to measure time taken by a function to execute. Times are expressed in milliseconds.
Here are the results from a single pass of five different approaches, two using ScriptDB, three writing to Spreadsheets, all with the same source data. (400 objects with 5 String & 5 Number attributes)
Spreadsheet.flush()In this experiment, the only difference from Experiment 1 was that we called Spreadsheet.flush() after every setValue/s call. The cost of doing so is dramatic, (around 700%) but does not change the recommendation to use a spreadsheet over ScriptDB for speed reasons, because writing to spreadsheets is still faster.
Note: This experiment was often killed with Exceeded maximum execution time.
You're reading this on the interwebs, so it must be true! But take it with a grain of salt.
If you want to perform (or better yet, improve) these experiments, create a blank spreadsheet, and copy this into a new script within it. This is also available as a gist.
/**
* Run experiments to measure speed of various approaches to saving data in
* Google App Script (GAS).
*/
function testSpeed() {
var numObj = 400;
var numAttr = 10;
var doFlush = false; // Set true to activate calls to SpreadsheetApp.flush()
var arr = buildArray(numObj,numAttr);
var start, stop; // time catchers
var db = ScriptDb.getMyDb();
var sheet;
// Save into ScriptDB, Object at a time
deleteAll(); // Clear ScriptDB
start = new Date().getTime();
for (var i=1; i<=numObj; i++) {
db.save({type: "myObj", data:arr[i]});
}
stop = new Date().getTime();
Logger.log("Elapsed time for ScriptDB/Object test: " + (stop - start));
// Save into ScriptDB, Batch
var items = [];
// Restructure data - this is done outside the timed loop, assuming that
// the data would not be in an array if we were using this approach.
for (var obj=1; obj<=numObj; obj++) {
var thisObj = new Object();
for (var attr=0; attr < numAttr; attr++) {
thisObj[arr[0][attr]] = arr[obj][attr];
}
items.push(thisObj);
}
deleteAll(); // Clear ScriptDB
start = new Date().getTime();
db.saveBatch(items, false);
stop = new Date().getTime();
Logger.log("Elapsed time for ScriptDB/Batch test: " + (stop - start));
// Save into Spreadsheet, Object at a time
sheet = SpreadsheetApp.getActive().getActiveSheet().clear();
start = new Date().getTime();
for (var row=0; row<=numObj; row++) {
var values = [];
values.push(arr[row]);
sheet.getRange(row+1, 1, 1, numAttr).setValues(values);
if (doFlush) SpreadsheetApp.flush();
}
stop = new Date().getTime();
Logger.log("Elapsed time for Spreadsheet/Object test: " + (stop - start));
// Save into Spreadsheet, Attribute at a time
sheet = SpreadsheetApp.getActive().getActiveSheet().clear();
start = new Date().getTime();
for (var row=0; row<=numObj; row++) {
for (var cell=0; cell<numAttr; cell++) {
sheet.getRange(row+1, cell+1, 1, 1).setValue(arr[row][cell]);
if (doFlush) SpreadsheetApp.flush();
}
}
stop = new Date().getTime();
Logger.log("Elapsed time for Spreadsheet/Attribute test: " + (stop - start));
// Save into Spreadsheet, Bulk
sheet = SpreadsheetApp.getActive().getActiveSheet().clear();
start = new Date().getTime();
sheet.getRange(1, 1, numObj+1, numAttr).setValues(arr);
if (doFlush) SpreadsheetApp.flush();
stop = new Date().getTime();
Logger.log("Elapsed time for Spreadsheet/Bulk test: " + (stop - start));
}
/**
* Create a two-dimensional array populated with 'numObj' rows of 'numAttr' cells.
*/
function buildArray(numObj,numAttr) {
numObj = numObj | 400;
numAttr = numAttr | 10;
var array = [];
for (var obj = 0; obj <= numObj; obj++) {
array[obj] = [];
for (var attr = 0; attr < numAttr; attr++) {
var value;
if (obj == 0) {
// Define attribute names / column headers
value = "Attr"+attr;
}
else {
value = ((attr % 2) == 0) ? "This is a reasonable sized string for testing purposes, not too long, not too short." : Number.MAX_VALUE;
}
array[obj].push(value);
}
}
return array
}
function deleteAll() {
var db = ScriptDb.getMyDb();
while (true) {
var result = db.query({}); // get everything, up to limit
if (result.getSize() == 0) {
break;
}
while (result.hasNext()) {
var item = result.next()
db.remove(item);
}
}
}
