JavaScript loop to copy JDBC query results to a 2D array
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
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.
javascript performance jdbc google-apps-script
edited Jul 31 at 17:27
200_success
123k14143398
asked Jul 31 at 14:33
user3585217
61
 |Â
show 1 more comment
up vote
1
down vote
favorite
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.
javascript performance jdbc google-apps-script
edited Jul 31 at 17:27
200_success
123k14143398
asked Jul 31 at 14:33
user3585217
61
1
Probably only a minor improvement and not a complete answer, but assumingrs.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!
– Daniel
Jul 31 at 21:44
Also you're never using therowvariable; can just remove that part.
– Daniel
Jul 31 at 21:45
1
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 eachrs.getStringcall is hitting the database.
– Gerrit0
Aug 1 at 2:20
Hi @Daniel - I try both recommendations.
– user3585217
Aug 1 at 6:56
@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.
– user3585217
Aug 1 at 6:56
 |Â
show 1 more comment
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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.
javascript performance jdbc google-apps-script
edited Jul 31 at 17:27
200_success
123k14143398
asked Jul 31 at 14:33
user3585217
61
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.
javascript performance jdbc google-apps-script
edited Jul 31 at 17:27
200_success
123k14143398
asked Jul 31 at 14:33
user3585217
61
edited Jul 31 at 17:27
200_success
123k14143398
edited Jul 31 at 17:27
200_success
123k14143398
edited Jul 31 at 17:27
200_success
123k14143398
123k14143398
asked Jul 31 at 14:33
user3585217
61
asked Jul 31 at 14:33
user3585217
61
asked Jul 31 at 14:33
user3585217
61
61
1
Probably only a minor improvement and not a complete answer, but assumingrs.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!
– Daniel
Jul 31 at 21:44
Also you're never using therowvariable; can just remove that part.
– Daniel
Jul 31 at 21:45
1
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 eachrs.getStringcall is hitting the database.
– Gerrit0
Aug 1 at 2:20
Hi @Daniel - I try both recommendations.
– user3585217
Aug 1 at 6:56
@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.
– user3585217
Aug 1 at 6:56
 |Â
show 1 more comment
1
Probably only a minor improvement and not a complete answer, but assumingrs.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!
– Daniel
Jul 31 at 21:44
Also you're never using therowvariable; can just remove that part.
– Daniel
Jul 31 at 21:45
1
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 eachrs.getStringcall is hitting the database.
– Gerrit0
Aug 1 at 2:20
Hi @Daniel - I try both recommendations.
– user3585217
Aug 1 at 6:56
@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.
– user3585217
Aug 1 at 6:56
1
1
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!– Daniel
Jul 31 at 21:44
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!– Daniel
Jul 31 at 21:44
Also you're never using the
row variable; can just remove that part.– Daniel
Jul 31 at 21:45
Also you're never using the
row variable; can just remove that part.– Daniel
Jul 31 at 21:45
1
1
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.– Gerrit0
Aug 1 at 2:20
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.– Gerrit0
Aug 1 at 2:20
Hi @Daniel - I try both recommendations.
– user3585217
Aug 1 at 6:56
Hi @Daniel - I try both recommendations.
– user3585217
Aug 1 at 6:56
@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.
– user3585217
Aug 1 at 6:56
@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.
– user3585217
Aug 1 at 6:56
 |Â
show 1 more comment
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f200666%2fjavascript-loop-to-copy-jdbc-query-results-to-a-2d-array%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
1
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!– Daniel
Jul 31 at 21:44
Also you're never using the
rowvariable; can just remove that part.– Daniel
Jul 31 at 21:45
1
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.getStringcall is hitting the database.– Gerrit0
Aug 1 at 2:20
Hi @Daniel - I try both recommendations.
– user3585217
Aug 1 at 6:56
@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.
– user3585217
Aug 1 at 6:56