Created
March 1, 2013 15:05
-
-
Save mogsdad/5065231 to your computer and use it in GitHub Desktop.
This gist contains a Google Apps Script that performs some experiments to provide comparisons between data storage options. It was written in response to StackOverflow question [What is faster: ScriptDb or SpreadsheetApp?](http://stackoverflow.com/questions/15145918/what-is-faster-scriptdb-or-spreadsheetapp)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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 | |
} | |
/** | |
* Delete all items from Database. | |
* From https://developers.google.com/apps-script/scriptdb#deleting_all_items_from_the_database | |
*/ | |
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); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment