Comparing all back ends performance

Some information on performance of each of the back ends in Database abstraction with google apps script

The library reference is 

Mj61W-201_t_zC9fJg1IzYiz3TLx7pV4j

Update – I’ve added Driver MongoLab since these original tests were made. For a more detailed analysis than here, see Back end performance linearity

Comparing performance

In Relative performance I covered performance of scriptDB versus Parse versus DataStore.  I have since improved the delete batching performance of Parse.com, so now parse comes out best on all counts between the 3. Since that time I’ve added DriverMemory as a backend – naturally this is the fastest, but it’s interesting to look at them all side by side. Note that this performance test is fairly naive. It simply deletes everything, recreates it and queries it. This is good for those with good batching capabilities (parse) and bad for those without (orchestrate). It also shows very poor performance for writing to Fusion Tables. The batching capabilities of Fusion are seriously hampered by SQL string length constraints as described in Multiple inserts in Fusion Tables – which might be something that can be resolved, but in general Fusion is fine for querying and deleting, but seriously sucks for inserting ( i had to split into two to avoid execution time exceeded for 1000 records). I was also surprised by how good both drive and sheets are as a datastore, but they will degenerate with querying of big datasets, and in the case of sheets,  deletion of interspersed rows. I was completely frustrated with Datastore and quota problems on the free tier, so it all depends on what you are going to be using this for, and whether you are sharing the underlying database with other apps that access them in different ways. I am also sure that I can and will continue optimizing and adding new drivers as we go along. I’ve spent most time tweaking Parse.com, so bear that in mind when looking at these. 

The results

In this example I’m copying 1000 records to each of the backends. Where possible,  each of these drivers using batching. For the purposes of the test, I’ve switched caching off in all cases. There are 3 operations performed – deleting 1000 records, adding 1000 records and querying them. Some of them I ran a few times and averaged, where the run times were very short. It doesnt include Driver MongoLab which I’ve added since, and which outperforms all of these (except memory).Now when I limit to the top 5, and add mongolab, and throw in lots of complex queries, deletions and other playing around as described in Some test cases for various backends, here’s the performance, mongolab comes out the clear winner after doing it all in memory. Note that we are measuring my implementation of the drivers here – some may be better than others.

Original summary data

Raw Data

Here’s the code

function getTheData() {    // get seed data from Drive  var driveHandler = new cDataHandler.DataHandler (     ‘seed.json’,                                  cDataHandler.dhConstants.DB.DRIVE,         undefined,      ‘/datahandler/driverdrive’);     assert(driveHandler.isHappy(), ‘unable to get driver handler’,’handler’);   var result = driveHandler.query ();  assert (result.handleCode >=0, result, ‘ getting seed data from drive ‘ + result.handleError);   return result.data; } function compareScriptDb() {  compareMethod (‘customers’,cDataHandler.dhConstants.DB.SCRIPTDB, ‘myddb’,ScriptDb.getMyDb()); } function compareDataStore() {  doGetPattern({} , constructConsentScreen, function (accessToken) {      compareMethod (‘customers’,cDataHandler.dhConstants.DB.DATASTORE, ‘xliberationdatastore’,undefined,accessToken);    } ,’googleDatastore’);  }function compareDrive() {  compareMethod (‘customers.json’,cDataHandler.dhConstants.DB.DRIVE, ‘/datahandler/driverdrive’);}function compareSheet() {  compareMethod (‘customers’,cDataHandler.dhConstants.DB.SHEET, ’13ccFPXI0L8-ZViHlv8qoVspotUcnX8v0ZFeY4nUP574′);}function compareFusion() {  compareMethod (‘1lOvQDzjosOzHnKlZP0xnwbTndbgxDEbSwUS5tseb’,cDataHandler.dhConstants.DB.FUSION, ‘customers’);}function compareMemory () {  compareMethod (‘customers’,cDataHandler.dhConstants.DB.MEMORY);} function compareParse () { var userStore = PropertiesService.getScriptProperties(); compareMethod (‘customers’,cDataHandler.dhConstants.DB.PARSE, ‘myparse’,JSON.parse(userStore.getProperty(“parseKeys”)));} function compareOrchestrate () { var userStore = PropertiesService.getScriptProperties(); compareMethod (‘customers’,cDataHandler.dhConstants.DB.ORCHESTRATE, ‘myorch’,JSON.parse(userStore.getProperty(“orchestrateKeys”)));} function getLogHandle() {  var logHandle =  new cDataHandler.DataHandler(‘comparison’,cDataHandler.dhConstants.DB.SHEET,undefined,’13ccFPXI0L8-ZViHlv8qoVspotUcnX8v0ZFeY4nUP574′);  assert(logHandle.isHappy(), ‘unable to get log handler’,’handler’);    return logHandle;}   function compareMethod ( silo , type , dbid, dbprop, accessToken ) {    var theData = getTheData();    // get a datastore handler  var theHandler = new cDataHandler.DataHandler (     silo,                              // Kind     type,    // Datastore     undefined,      dbid,                   // project id     dbprop,      undefined,     false,                                     // analytics opt out     ‘comparing’,                              // analytics debugging tracking      accessToken,                              // the access token     true);                                    // disable caching for testing    assert(theHandler.isHappy(), ‘unable to get compare handler’,’handler’);     // log it here  var logHandle = getLogHandle();        // copy it to   deleteAndCopy_ (theHandler, theData);    // delete whats there, insert and query – can be shared across handlers  function deleteAndCopy_ () {    // delete all current   record_ (‘remove’);        // save new   record_ (‘save’,theData);        // check   var result = record_ (‘query’);   assert (result.data.length === theData.length,result,’checked length’);   }    function record_ (what,ar) {    var then = new Date().getTime();    logHandle.save({at:then,event:’start’,what:what,where:theHandler.getDBName()});    var result = theHandler[what](ar);    var now = new Date().getTime();    logHandle.save({at:now,event:’end’,what:what,where:theHandler.getDBName(),code:result.handleCode,elapsed:now-then});    assert (result.handleCode >=0, result, what + ‘ data from ‘ +  theHandler.getDBName() );    return result;  }}

Summary

All round, I’d go for Driver MongoLab as a standalone GAS replacement for ScriptDB. See more like this in Database abstraction with google apps script and Back end performance linearity