Google Apps Script JDBC ResultSet to Array

Question

Is there a better way of retrieving the results from a ResultSet? Calling getString() on each value is very slow. It takes up to 2.5 seconds to put about 400 rows, 16 columns into an array before I can use it.

The query itself only takes about 80ms which is faster than accessing a Google Sheet (about 2 seconds) but it takes too long to read the data.

This is what I'm using now.

  var results = stmt.executeQuery();
  var numCols = results.getMetaData().getColumnCount();
  var resultsArray = [];
  var count = 0;
  while(results.next()) {
      resultsArray.push([]);
      for (var i = 1; i <= numCols; i++)
          resultsArray[count].push(results.getString(i));
      count++;
  }

Answer 1

Try setting setFetchSize() to all the rows required.

results.setFetchSize(400);//for 400rows
results.setFetchSize(4000);//for 4000rows

Answer 2

Modifying the query might help to speed up the process. With MS SQL Server for example you can add FOR JSON at the end. So there will be no columns. Recieving the result in GAS will look like this:

let conn = Jdbc.getConnection('your connection string');
let statement = conn.createStatement();
let results = statement.executeQuery('your query');
let resultJSON;
while (results.next()) resultJSON += results.getString(1);
let resultObject = JSON.parse(resultJSON);

While loop is here because the JSON string might be splitted over some number of rows.

Answer 3

This answer is not directly related to the question, but might help other with similar problem today.

Since V8 the reading time is even higher and going back to Rhino could solve the problem (temporarily). You just have to deactivate : Setting > Run V8

Put a star here if you want to be updated : https://issuetracker.google.com/issues/191684323