GoogleScript loading JDBC ResultSet into Array is very slow / how to optimize?

Question

I'm using JDBC on GoogleScript to communicate with GoogleCloud MySQL.

My table has 24 columns and it takes about 30 seconds for a 1000 row ResultSet to be loaded into the array. The loop in the code block below takes up most of that time.

Did I make any mistakes that caused it to be this slow? Is there a way to optimize this? Are there better approaches?

var results = stmt.executeQuery(query);

  results.last();
  var nrow = results.getRow()
  results.first();
  var ncol = results.getMetaData().getColumnCount();

  var dat = new Array(nrow);
  for (var i = 0; i<nrow; i++){
    dat[i]=new Array(ncol);
    for(var j = 0; j<ncol; j++){
      dat[i][j] = results.getString(j+1);
    }
    results.next();
  }

Newark

This is not that much different than what you are doing but it might be little faster. developers.google.com/apps-script/guides/… – Fluoridate

I need the query result to be in a 2d array instead of Logger entries :/ – Newark

Answer 1

Had the same problem and realized that it was caused by the new V8 runtime migration. I went back to the old one and it worked, it got faster. I don´t really know why it's not working well with the new version... it's supposed to be better =/

Anyway, you can change back to the old Rhino version in the menu Run > Disable new Apps Script powered by V8.

Hope this helps. Good luck!

Luteous

Why????? At least it works now. Lost half of day. But saved some days. Thank you. – Invigorate

For me this setting did not improve script runtime. Importing 500 rows from MySQL takes ~ 7 minutes. – Glauconite

sucks that that helped but wow! saves me minutes per query – Ketch

Same problem here, solution works. Thanks ! – Oniskey

Answer 2

I too found myself with this issue. Perhaps it's already been resolved for you, but in any case and for any potential future user with the same issue I thought I'd share my findings.

Working with the google support team the JDBC connection couldn't be improved materially. It took 2-4 seconds to just establish a connection and google apps script simply didn't allow for any efficient ways of storing the JDBC results into an array. According to the Google support team it seems to just be an inherent limitation.

In my case switching back to Rhino didn't actually affect it very much, unlike some other posters/readers.

I ended up switching to using google sheet as a database and by way of comparison it averages ~1.00 second. Previously, this would take between 30-120 seconds. My dataset was ~2000 rows with about 8 columns, mostly text, ~90-100KB size-wise.

Receptionist