Google Cloud mySQL from Apps Script wildly varying performance

Question

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()

Answer 1

A bit late, but my solution to this was to ditch Google's SQL implementation altogether. I converted the app to use a FireBase realtime database, and that has MUCH better performance. As a bonus, it's also free (for our small usage), where as we had to pay for the Google Cloud SQL server before.

Answer 2

I have the same problem with the "next().. method".

My Logs:

19:27:51    SELECT `history_id` FROM `call_history` WHERE `context_start_time` BETWEEN '1710173130' AND '1714382429' LIMIT 200
19:27:51    // Total time for 5 iterations: 243730 milliseconds
19:27:51    // Average time per iteration: 48746 milliseconds
19:28:39    [(Jdbc:928:5)]  close 0
19:28:39    [(Jdbc:933:3)]  Time for 'queryMySqlTableAsObject': 48465
19:28:39    Items found: 200

This issue is still relevant on the Google issue tracker (despite the "Fixed" status) and is being discussed here: https://issuetracker.google.com/issues/298658393, and here, https://issuetracker.google.com/issues/236832481

or you can use the search in the issue tracker.

I resolved the issue using an external library that processes requests, and I have posted it here: https://github.com/githnow/JdbcX.

You can install it and compare the results yourself.

My logs after:

10:17:04    SELECT `history_id` FROM `call_history` WHERE `context_start_time` BETWEEN '1707598800' AND '1715806799' LIMIT 200
10:17:09    Execution time is 5284ms
10:17:12    Execution time is 3054ms
10:17:15    Execution time is 3015ms
10:17:18    Execution time is 3030ms
10:17:21    Execution time is 3055ms
10:17:21    // Total time for 5 iterations: 17434 milliseconds
10:17:21    // Average time per iteration: 3486.8 milliseconds