Some best practise notes on DbAbstraction usage

   

Since we have a lot of operations going through the database abstraction libraries nowadays (over 3 million since I released this in summer last year),  I thought I’d give a few hints.

I’ll use a handler for a spreadsheet in these examples, but the following comments mostly apply to all flavors backend databases.

Locking

The most expensive business is locking.  I use a system of named locks which use various techniques to ensure that operations are protected in a multiuser environment. By default, locking is enabled – but you don’t have to use it. If you are sure you are the sole user of a dataset, then disable locking when you get a handler. This avoids a whole bunch of checking. You disable locking like this

var handle = new cDbAbstraction.DbAbstraction(cDriverSheet, {
  siloid:'my table',
  dbid:'1lbx-wcaZood1AZfAfHCAJDRFEXy2EnorJEI_-2pbISw',
  locking : cDbAbstraction.ENUMS.LOCKING.DISABLED
});

Caching

Caching is very useful when you are doing lots of the same queries, especially in a  multiuser environment. It’s usually faster, and it saves using up quotas. However, like locking, it takes time to write to cache, and also to invalidate cache when there has been a write. If there is no likelihood of a cache benefit, then disable it.

var handle = new cDbAbstraction.DbAbstraction(cDriverSheet, {
  siloid:'my table',
  dbid:'1lbx-wcaZood1AZfAfHCAJDRFEXy2EnorJEI_-2pbISw',
  disablecache: true
});

Transactions

Since the API is ‘stateless’, each operation has to go through whatever mechanism it needs to use to get initialized, and also kick off locking and caching if these are enabled. If you have a group of operations that you want to operate as a single unit, you can wrap them in a transaction. Aside from locking out updates in the middle of a set of operations, transactions also avoid the startup and shutdown costs associated with each operation since they are only done at the beginning and end of the transaction. Transactions haven’t been fully implemented in all back ends yet, but in those that have been enabled, rollback in the case of a failure is also automatically included. The downside of transactions is that in a multiuser environment, everyone else will be locked out for the duration of the transaction.

Here’s an example of a transaction

var result = handler.transaction ( function(db,options) {

  var result = db.save ( [{a:1,b:2 },{b:10,a:23}] );
  if (result.handleCode < 0) {
    throw 'Failed save' + JSON.stringify(result);
  }

  var result = db.query ();
  if (result.handleCode < 0) {
    throw 'Failed query ' + JSON.stringify(result);
  }

  return result;
});

if (result.transaction.code < 0) {
  throw 'Failed transaction ' + JSON.stringify(result);
}

UA tracking

As described in library instrumentation, I track library usage with Google Analytics measurements protocol so I can see which versions are being used, and how often. That’s how I was able to say how many operations there have been using these libraries at the beginning of this post. This takes up some resource of course, since it has to signal the Google Analytics service that something is going on. This signal both uses UrlFetchApp quota, and takes a little time – about 10-15 milliseconds per call. This is not much in the context of a database call, and no tracking is done for queries that are satisfied by cache. However you can turn it off if you want – but it means I won’t be able to have an accurate view of driver usage.

Here’s how to turn it off.

var handle = new cDbAbstraction.DbAbstraction(cDriverSheet, {
  siloid:'my table',
  dbid:'1lbx-wcaZood1AZfAfHCAJDRFEXy2EnorJEI_-2pbISw',
  optout: true
});

Batching

These libraries have been written with batching in mind, It’s always better to operate on a bunch of things rather then one thing at a time.

So this

  // do this
  var a = [];
  for (var i =0; i < 20; i++) {
    a.push ({index:i});
  }
  handler.save (a);

is twenty times better than this

  // don't to this
  for (var i =0; i < 20; i++) {
    handler.save ({index:i});
  }

For more stuff like this, take a tour of the desktop liberation site, or join our community.