javascript - JDBC ResultSet to Array is very slow - Stack Overflow

admin管理员组

文章数量:1400771

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++;
  }

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++;
  }

• javascript
• performance
• google-apps-script
• jdbc

Share Improve this question Follow edited Apr 3 at 22:48 TheMaster 51.2k77 gold badges7272 silver badges9999 bronze badges asked Oct 12, 2014 at 5:23 JackJack 5,7681212 gold badges5151 silver badges7575 bronze badges 5

• Were you able to find a solution to this? – Saqib Ali Commented May 24, 2018 at 19:56
• a nice example, anyways :) – Mike M Commented Oct 8, 2019 at 12:59
• 1 Anyone found a solution? Taking 50 seconds for ~1,5k rows and ~140 cols – peter_b Commented Feb 18, 2020 at 12:45
• Can you use getArray? I don't have experience with Jdbc but I saw this function and thought it might help? – IMTheNachoMan Commented Mar 23, 2020 at 4:09
• Where is your SQL server located? Is there anything that you can do to your query to limit the number of rows that you are getting so it's not pulling across additional data that you don't need and see if that speeds it up? i.e. in lieu of a * query select only the rows that you need? I had a slow performing query in one of mine and I realized that there was a data column I was pulling across that was sending extraneous info that wasn't needed. I eliminated that and it sped it up considerably. – Rob Commented Jul 21, 2020 at 0:19

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 1

Try setting setFetchSize() to all the rows required.

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

Interestingly, adding console.log(results.getRow()) to the inside of my while() loop dramatically increased the speed of the results.getString(i). Perhaps that would help you as well:

while(results.next()) {
 console.log(results.getRow());
 resultsArray.push([]);
 for (var i = 1; i <= numCols; i++)
  resultsArray[count].push(results.getString(i));
 count++;
}

Hope this helps anyone else with the same problem!

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.

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

https://issuetracker.google./issues/191684323

EDIT:

Rhino is not a good idea anymore, and start to act weirdly such as logs not displayed and no answers from API calls.

I've found a solution with Cloud Function, it require access to Cloud SQL, and called by Apps Script with Urlfetch. It's very fast and can be secured to be only available for some users.

To start, I would remand to follow: https://cloud.google./sql/docs/mysql/connect-functions

Example of Cloud Function (with Node.js) :

exports.connect= async (req, res) => {

    var mysql = require('promise-mysql');
    var pool = await mysql.createPool({
        socketPath      : '/cloudsql/' + PROJECT_ID,
        user            : USERNAME,
        password        : PASSWORD,
        database        : BASE
    });


        pool.getConnection(function (err, conn) {
            if (err) {
                return res.status(400).send('bad connexion');
            }
            conn.query('SELECT * FROM TABLE_EXAMPLE', function(err, rows) {
                if (err) {
                    return res.status(400).send('No connexion');
                }
                res.status(200).send(rows);
                conn.release();
            })
        });
};

本文标签： javascriptJDBC ResultSet to Array is very slowStack Overflow