jdbc performance is terrible in v8?

[Sean Hurley]

Hello - 

Doing a simple read from mysql, using results.next(), and grabbing each column... performance is atrocious - many seconds to get 100 rows.

Rhino was a little better, but still nearly unusable for any kind of reasonable data volumes.

Anyone else running into this?

[dimud...@gmail.com]

The performance of Apps Script's JDBC connector has never been stellar. But it should works well enough for small data sets.

How many columns do you have per row? Do one or more of those columns hold large blobs of data?

It could be an issue with your MySQL server. Have you tried connecting to it and making queries via a different client to benchmark latency?

[Sean Hurley]

Thanks so much for the thoughts here!

Tested on 100 rows, 40 columns, largely varchars, but none of them larger than about 50. Response time on v8 was about 3-5seconds after the connection was established, and about 1-2 on rhino.

Is there another way to hit a mysql database from appscript? Is this a fundamental limitation of the platform?

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/9Jj7DpujoW0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/80a97395-5cd1-47db-a99c-563559c4a30fn%40googlegroups.com.

--

Sean Hurley

802-376-1737

seanp...@gmail.com

[Klaro Team]

Hi Sean, 

I have just run into the same issue and tried multiple different ways of getting around the recommended "looping" of results without any luck.

the loop is so bad but so far its the only function that apps script has accepted. All other attempts like the below and then using the function to “data” via const klaroData = processResults(results):

function processResults(results) {
function createRow(result) {
return {
source: result.getString(‘source’),
title: result.getString(‘title’),
data_value: result.getString(‘data_value’),
unit: result.getString(‘unit’),
description: result.getString(‘description’)
};
} return results.toArray().map(createRow);
}

or using a getBlob, etc are not working as I get error “is not a function”. Supposedly Apps script is javascript-based but seems like many applicable functions don’t seem to work based on the JDBC result that comes back. The above being one example that it didn’t like. 

How did you go? have you found a way forward? I will post any of my findings here for you if you still have this problem. I know its been 2 years since last so I assume you either moved on or found something that did the trick.

[Klaro Team]

Hi All,

I thought I'd share an update from my end, hopefully this can be helpful to any of you.

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.

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.

Hopefully this is a method that can work for you too!