'JDBC results set iteration sometimes goes fast and often times goes slow (75x speed difference)

I'm developing a GAS script to retrieve data (~15,000 rows) from an Azure SQL database table into a Sheets spreadsheet. The code works fine but there are huge speed differences from run to run in the results.next() loop

Below is my code (some variable declarations and private stuff removed) and below the code is logs from three executions

function readData() {

    Logger.log('Establishing DB connection')
    let conn = Jdbc.getConnection(connectionString , user, userPwd);

    Logger.log('Executing query')
    let stmt = conn.createStatement();
    let results = stmt.executeQuery("SELECT * FROM VIEW");


    let contents = []
    let i = 0

    Logger.log("Iterating result set and adding into array")
 
    while (results.next()) {
        contents.push([
        results.getInt(1),
        results.getString(2),
        results.getInt(3),
        results.getString(4),
        results.getInt(5),
        results.getString(6),
        results.getString(7),
        results.getString(8),
        results.getFloat(9),
        results.getFloat(10),
        results.getInt(11),
        results.getString(12),
        results.getInt(13),
        results.getInt(14),
        results.getInt(15),
        ])

        //Make log entry every 100th iteration and display the average passed ms per iteration 
        i++
        if(i % 100 == 0){
            Logger.log(i)
            finish = new Date().getMilliseconds();
            Logger.log((finish - start) / i)
        }
    }



    sheet.getRange(2,1,sheet.getLastRow(),15).clearContent()

    sheet.getRange(2,1,contents.length,15).setValues(contents)

    results.close();
    stmt.close();
}

Fast run:

8:41:47 AM  Info    11100 Records added
8:41:47 AM  Info    8.43ms on average per record
8:41:47 AM  Info    
8:41:47 AM  Info    11200 Records added
8:41:47 AM  Info    8.42ms on average per record
8:41:47 AM  Info    
8:41:48 AM  Info    11300 Records added
8:41:48 AM  Info    8.42ms on average per record

Slow run:

8:48:01 AM  Info    100 Records added
8:48:01 AM  Info    162.30ms on average per record
8:48:01 AM  Info    
8:48:17 AM  Info    200 Records added
8:48:17 AM  Info    162.84ms on average per record
8:48:17 AM  Info    
8:48:34 AM  Info    300 Records added
8:48:34 AM  Info    163.11ms on average per record

Extremely slow run:

8:56:46 AM  Info    300 Records added
8:56:46 AM  Info    629.08ms on average per record
8:56:46 AM  Info    
8:57:49 AM  Info    400 Records added
8:57:49 AM  Info    628.95ms on average per record
8:57:49 AM  Info    
8:58:52 AM  Info    500 Records added
8:58:52 AM  Info    629.70ms on average per record

So as seen from above logs, one run of the script can go roughly 75x faster than another. The time per iteration stays the same within a specific run. I'm pretty baffled as to how that's possible. Is there something about the result set object I don't know?

Solution 1:^[1]

You can submit a bug on Google's Issue Tracker using the following template for Apps Script:

https://issuetracker.google.com/issues/new?component=191640&template=823905

If you have a workspace account, you can also contact Google Workspace support so they can take a look at your issue.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Kessy

'JDBC results set iteration sometimes goes fast and often times goes slow (75x speed difference)

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]