Quote:
Originally Posted by Caleb Sykes
Do you have a good idea of which operation(s) are taking up the most time for your program? You mentioned 45 seconds total runtime earlier, if you could break this down a bit more we could find out where to make improvements.
Most of this time probably comes from the createFormula function, but it would be good to check before working on alternative solutions to the [A][x]=[b] equation.
|
My last run took about 50 seconds to finish, approximately broken down as follows:
1sec: get sheets, get ranges, initialize arrays, etc.
23sec: get unique team list, get alliance teams (T) and scores (b) in 2Mx3 and 2Mx1 formats respectively
printing team list was nearly instantaneous so I couldn't get a time on that
25sec: creating and printing out A matrix (2MxT)
printing b, creating/printing the formula, and doing the calculations for the formula were all very fast (i.e. fast enough I couldn't time them)
These times were all done by hand, I'll see tonight if I can add in a timer, but I think these give the rough percentages. I believe most of the time is taken up by looping through arrays, especially T while looking for unique teams and creating the A matrix. I could probably print A and b directly instead of creating the alliances/scores arrays which would save some time. If you have any suggestions on what else to try, let me know.
Quote:
Originally Posted by Ether
LINEST(alliance_scores_column_vector, 2M-by-T_design_matrix_A, FALSE, TRUE)
If that last parameter is true, you get not only the least-squares model coefficients (OPRs), but also a lot of extra statistics (that you may or may not want).
|
I don't think much time is actually taken by the final calculation, but this is definitely cleaner.
However, is there a reason this is giving me team OPRs in the reverse transpose of the team vector? (And a reason for the added zero at the end?)
(i.e. i'm getting teamNOPR, teamN-1OPR, ... team1OPR, 0 instead of team1OPR \n team2OPR \n ... teamNOPR. I did take the transpose to flip it, but it's still upside down)
Quote:
Originally Posted by Ether
If you find that your code for creating A, b, and T is the time-consuming culprit, try creating the T-by-T N matrix and the T-by-1 d column vector directly, rather than computing them from A and b.
|
Is N = [A^T][A], where each row corresponds to the sum of all the rows from A where a given team played? (and thus d is [A^T][b], or the sum of all the alliance scores from matches where that team played)
I'm not sure if this would necessarily be much faster, since it'd still require looping through the team matrix to form N (and now it'd require it to form d too), but I can definitely try it.