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.


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


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.


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, every one else will be locked out for the duration of the transaction.

Here’s an example of a transaction

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.


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

is twenty times better than this

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

About brucemcp 223 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


three + nine =