Improve google apps script execution

Question

I am using the below code to write data from a google spreadsheet to a mySQL database table and this script used to work fine when it had around 4000 records now it has over 8000 records and runs very slow. Is there a work around to read all the google spreadsheet data into memory and then write it to the MySQL database.

function myfunction() { 
  var colA;
  var colB; 
  var colC; 
  var colD;
  var colE;  

  var mysqldb = Jdbc.getConnection("jdbc:mysql;dbipaddress","user","pa$$word");
  var sql = mysqldb.createStatement();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1'); 
  var data = sheet.getDataRange().getValues();  

  mysqldb.setAutoCommit(false) 

  var query = "{call [dbo].[sp_copygsheets](?,?,?,?,?)}";
  sql = mysqldb.prepareCall(query);

  for (var i = 1; i < data.length; i++) {
  colA = data[i][0];
  colB = data[i][1]; 
  colC = data[i][2]; 
  colD = data[i][3]; 
  colE = data[i][4];  

  sql.setString(1, colA);
  sql.setString(2, colB); 
  sql.setString(3, colC); 
  sql.setString(4, colD);
  sql.setString(5, colE); 
  sql.addBatch();
  }

  sql.executeBatch();
  mysqldb.commit();

  sql.close();
  mysqldb.close();
}

Answer 1

As far as I know, when you are using

var data = sheet.getDataRange().getValues();

it reads all of the data from your "sheet1" sheet in one chunk into memory, so the way you are doing the data retrieval/reading seems as fast as it can be. However, note that getDataRange() reads the rectangle that contains text into memory, so you may be able to optimize this if you can make assumptions about your data, for example, how many columns you have - in which case you can read only certain parts into memory by using getRange(row, column, numRows, numColumns) instead.

I think what may be slowing you down is your for loop on the transformation part, but I am not sure, so you may be able to track down the source of your performance issues by using this code to measure time performance:

var start = new Date().getTime();

// Insert test code here

var end = new Date().getTime();
Logger.log(end - start);