Google Apps Script JDBC Google Cloud SQL performance problems

Viewed 599

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]

0 Answers
Related