|
Jul. 28th, 2009|10:24 am |
Everything is fast for small n.
I have a macro that was okay for small n (300 rows - maybe 5 minutes; not efficient, but if you're only going to run it once, it isn't a big deal), but got awful at an n even a little larger (1000 rows - I'm estimating, based on as much as I could stand to watch of the process, 10 hours).
So. Optimisation time!
Step 1A: Toss in the standard "Don't refresh the screen" instructions everywhere.
Step 1B: Discover that the script is still slow, funnily enough.
Step 2: Toss in lots of Print statements to figure out what is taking time that shouldn't. Answer: the macro consists of a set of four sub macros, each of which performs a similar set of operations: empties a series of cells and then inserts updated values. Both steps were unreasonably (and untenably) slow.
Step 3: Change the way the first half of the script operates. Its previous implementation was a loop that goes from i to n and changes the value of three cells in the row to empty to a set of three "Empty this column between x and y cells" operations. Each iteration of the loop (for n iterations) was taking 4 seconds. So this brought this particular step down from a theoretic hour-long task, to a less than one second task. And that type of thing was happening four times! (Hooray for modularisation!)
Obviously, Step 3 had cut out an awful lot of the time taken for the macro to run; but the second task for the script was harder to optimise simply: it's another set of loops (at the most vicious level, three nested loops - two of which are decrementing rather than incrementing), and there's no included functionality that will perform the same sort of task built-in, as far as I know.
So, how to improve the second step?
Step 4: As the macro doesn't rely on progressively updated values (unlike some of my other ones), I simply disabled automatic cell recalculation (and then re-enabled it at the end of the script). Suddenly my multi-hour script takes less than a second. Whoo! Go me!
Bow before glory! ...I detect lack of bowing!
-Mmaster |
|