1
\$\begingroup\$

I have a JS running in Google App Script, but for me, it's like an overall example/problem I ran into more often. The script fetches via JDBC the results of a MySQL query. The result has about 60.000 rows. Fetching that goes quite fast. After that I take the result and use this JS code to write it into a 2D array:

var rangeArray = [];
var row = 1; 
while (rs.next()) {
   var tempArray = [];
     for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
       tempArray.push(rs.getString(col + 1));
     }
   rangeArray.push(tempArray);
   row++;
}

So it's this loop, which costs most time. Fetching the query result itself executes in about 12 seconds - which is pretty much the execution time of the MySQL server itself.

However, fetching the query results AND write them to the array takes 10 minutes. I am not surprised that it takes that long but is there a principal way to avoid such loops and create the array more effectively.

\$\endgroup\$
  • 1
    \$\begingroup\$ Probably only a minor improvement and not a complete answer, but assuming rs.getMetaData().getColumnCount() doesn't change from one call to the next, you could pull that out into a variable and only calculate it once. Saving 59,999 calls to that will probably at least help a bit! \$\endgroup\$ – Daniel Jul 31 '18 at 21:44
  • \$\begingroup\$ Also you're never using the row variable; can just remove that part. \$\endgroup\$ – Daniel Jul 31 '18 at 21:45
  • 1
    \$\begingroup\$ It would be really helpful to have a bit more context, I don't have much experience with Google Apps Script, but I believe that 60000 calls should be on the order of seconds at most... and more likely less than a second. It sounds to me like each rs.getString call is hitting the database. \$\endgroup\$ – Gerrit0 Aug 1 '18 at 2:20
  • \$\begingroup\$ Hi @Daniel - I try both recommendations. \$\endgroup\$ – urban-a Aug 1 '18 at 6:56
  • \$\begingroup\$ @Gerrit0 no no it's definitly only one call. The call is a query with 83 UNION calls so that takes time, also directly on the MySQL server. When I run the script without writing to the array, it's done in seconds. rs is the results object and getString is just extracting the certain row of a column. \$\endgroup\$ – urban-a Aug 1 '18 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.