I have a fairly simply Google Apps Script web app, that uses the JDBCGoogleCloud connection to connect to a Google cloud mySQL database. It’s a system that allows users at a conference to do things like vote, mark their attendance, and indicate they want to speak. It runs as an embedded web applet on a Google Site.

There are only a dozen or so tables, and the largest table is around 200 rows with around 6 columns. Data is mostly varchar’s, a timestamp, and some tinyint’s.

This is working fine, but performance varies wildly. I can run the same “SELECT * FROM TABLENAME” query 10 times in a row, and it will vary from around 1 second, to as much as 70 seconds, to return the data each time.

Any thoughts on why this is? it makes the system hard to rely on.

I also tried using the normal JDBC.getConnection and pointed it to a lab mySQL server here in house. I see the same behaviour. Sometimes it executes in a second or two, other times it’s dozens of seconds.
If I instead point the code to a Google Sheet instead to query from sheets, it’s consistently much faster (fractions of a second). This however brings other issues when writing to the sheets.

I guess I can try looking for a 3rd party addon for Google Apps script that speaks to mySQL to see if it has the same issue, or if it’s something with Google’s implementation.

But I’m really just looking to see if others are experiencing similar issues?

Sample bit of code:

var myDBInfo = getDBSettings()
var conn = Jdbc.getCloudSqlConnection(myDBInfo.ConnString,myDBInfo.DBUser, myDBInfo.DBPassword)
var stmt = conn.createStatement()
 
var query = "SELECT * from Attendance" 
rs = stmt.executeQuery(query)
  
//Get columns
var meta=rs.getMetaData();
var cols=meta.getColumnCount();
var info=[];
for( var i =1;  i <= cols; i ++ ) 
{
  info[i] = { label:meta.getColumnLabel(i), type:meta.getColumnTypeName(i) }
}
//get data
var ret = [];
while( rs.next()) 
{
  var row = {};
  for( var i=1;  i <= cols; i ++ ) 
  {
    row[info[i].label] = rs.getString(i)
  } 
  ret.push( row );
} 

rs.close()
stmt.close()
conn.close()