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

removesavequery
PARSE12.112.42.0
DRIVE8.02.81.0
MEMORY0.50.60.5
SCRIPTDB112.852.024.0
FUSION3.5387.113.5
ORCHESTRATE103.095.04.6
SHEET1.22.81.3
DATASTORE17.620.74.1

raw data

ateventwhatwherecodeelapsed
1405947425068startremoveMEMORY
1405947425530endremoveMEMORY0462
1405947426017startsaveMEMORY
1405947426695endsaveMEMORY0678
1405947427272startqueryMEMORY
1405947427813endqueryMEMORY0541
1405947446504startremoveMEMORY
1405947446991endremoveMEMORY0487
1405947447444startsaveMEMORY
1405947448040endsaveMEMORY0596
1405947448454startqueryMEMORY
1405947448987endqueryMEMORY0533
1405947456497startremoveSHEET
1405947457697endremoveSHEET01200
1405947458107startsaveSHEET
1405947461129endsaveSHEET03022
1405947461549startquerySHEET
1405947462873endquerySHEET01324
1405947648192startremoveSHEET
1405947649409endremoveSHEET01217
1405947649982startsaveSHEET
1405947652565endsaveSHEET02583
1405947652978startquerySHEET
1405947654347endquerySHEET01369
1405947698376startremoveDRIVE
1405947706875endremoveDRIVE08499
1405947707343startsaveDRIVE
1405947709896endsaveDRIVE02553
1405947710341startqueryDRIVE
1405947711321endqueryDRIVE0980
1405947726205startremoveDRIVE
1405947733721endremoveDRIVE07516
1405947734189startsaveDRIVE
1405947737152endsaveDRIVE02963
1405947737625startqueryDRIVE
1405947738576endqueryDRIVE0951
1405948513593startremoveORCHESTRATE
1405948615322endremoveORCHESTRATE0101729
1405948615989startsaveORCHESTRATE
1405948710888endsaveORCHESTRATE094899
1405948711421startqueryORCHESTRATE
1405948716975endqueryORCHESTRATE05554
1405948729670startremoveORCHESTRATE
1405948833999endremoveORCHESTRATE0104329
1405948838583startsaveORCHESTRATE
1405948933679endsaveORCHESTRATE095096
1405948937852startqueryORCHESTRATE
1405948941578endqueryORCHESTRATE03726
1405948985877startremovePARSE
1405948997960endremovePARSE012083
1405949003186startsavePARSE
1405949016045endsavePARSE012859
1405949016604startqueryPARSE
1405949018543endqueryPARSE01939
1405949051646startremovePARSE
1405949063703endremovePARSE012057
1405949064567startsavePARSE
1405949076489endsavePARSE011922
1405949076980startqueryPARSE
1405949079096endqueryPARSE02116
1405949094111startremoveSCRIPTDB
1405949206937endremoveSCRIPTDB0112826
1405949207460startsaveSCRIPTDB
1405949259461endsaveSCRIPTDB052001
1405949260008startquerySCRIPTDB
1405949283965endquerySCRIPTDB023957
1405949911928startsaveFUSION
1405950299013endsaveFUSION0387085
1405950767326startqueryFUSION
1405950780836endqueryFUSION013510
1405951052239startremoveFUSION
1405951055754endremoveFUSION03515
1405953241366startsaveDATASTORE
1405953262652endsaveDATASTORE021286
1405953263926startqueryDATASTORE
1405953268121endqueryDATASTORE04195
1405953343972startremoveDATASTORE
1405953361598endremoveDATASTORE017626
1405953362236startsaveDATASTORE
1405953382381endsaveDATASTORE020145
1405953383608startqueryDATASTORE
1405953387704endqueryDATASTORE04096

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.



For help and more information join our forum,follow the blog or follow me on twitter .

Comments