Multiple inserts in Fusion Tables

Inserting rows with the Google Apps Script FusionTables service is pretty slow. There are also a bunch of quotas to worry about. Here's what is says.  

Note: You can list up to 500 INSERT statements, separated by semicolons, in one request as long as the total size of the data does not exceed 1 MB and the total number of table cells being added does not exceed 10,000 cells. If you are inserting a large number of rows, use the import method instead, which will be faster and more reliable than using many SQL INSERT statements.
 
However, I can't get anywhere close to that without getting the 'server error' problem. For the moment, I'm still trying to find the optimum combination, but here's how to chunk up inserts. Note I'm also using Backing off on rate limiting to deal with rate limiting of fusion requests. Assuming that array q is full of insert statements, need to tweak these to get the best batching.

  var MAXCHUNKSIZE = 1000;
  var MAXINSERTS = 50;

// save it
        var  p=0;
        while (p < q.length) {
  
          var chunk = [];
          // this is about batching insert statements, but there are lots of quotas
          // we always do at least one, and let fusion deliver the size news if one transaction is too big
          for ( var chunkSize =0 ; chunk.length === 0 || (chunkSize < MAXCHUNKSIZE && chunk.length < MAXINSERTS && p+chunk.length < q.length ) ; p++ ) {
              chunk.push(q[p]);
              chunkSize += q[p].length;
          }

          sqlString = chunk.join(";")+";"
          
          // write it
          var r = parentHandler.rateLimitExpBackoff ( function () { 
            return FusionTables.Query.sql(sqlString); } ) ;
        }  

For help and more information join our forumfollow the blogfollow me on twitter

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.



Comments