I am using Google Cloud SQL to store data from my Google Apps Script using a JDBC connection but it is darn slow. I already tried Google's JDBC-tunnel and the normal JDBC-connection with IP-whitelisting. With both methods, one connection cycle takes about 900ms, in comparison I even get a better performance writing/reading to a spreadsheet. Table only has 20 columns and a few test-entries (10 rows).
It seems using GAS you can't pool your connection in the cache as it only saves strings and if you stringify/parse to save/read it won't work afterwards, so i'm left with opening a new connection for each read/write instance.
I use the SQL to store userstates from my Telegram-Bot, code is simple, on message read userstate, process data and write userstate, it's only 2 JDBC-connections per user-command but I get a reaction-time of nearly 2sec.
Code:
var dbUrl = "jdbc:google:mysql://myinstance/mytable";
var username = "myusername";
var password = "mypassword";
function jdbcGetUserState(chatid) {
var conn = Jdbc.getCloudSqlConnection(dbUrl,username,password);
var stmt = conn.prepareStatement("SELECT * FROM user_active WHERE chatid=?");
stmt.setInt(1,chatid);
stmt.setMaxRows(2);
var rs = stmt.executeQuery();
var data = {};
if (rs.next()) {
data =
{
activejobnumber: rs.getInt("activejobnumber"),
isadmin: rs.getBoolean("isadmin"),
...,
}
}
stmt.close();
conn.close();
return data;
}
EDIT: it seems establishing a new connection is very resource-intensive and takes nearly 900ms. Is there a workaround to JDBC connection pooling in apps script?
[18-09-10 09:40:12:507 PDT] Jdbc.getCloudSqlConnection([jdbc:google:mysql:/...) [0.837 seconds]