cfExcelProxy - Optimizing A Query Dump

I've been trying to finalize the rewritten version of cfExcelProxy, and one of the final obstacles I'm trying to get across was reducing the time required when populating an Excel page. In most situations, processing time is rather short. However, in one of my examples I need to dump a query with 4000 rows and 25+ columns... that's over 100k different cells!

In my first draft, the setCellsFromQuery method simply looped over the query and columnlist and turned the value over to the setCell method. Using this logic, the setCellsFromQuery method took a whopping 120 seconds to run! I don't know about you, but that's a little too much. Looking through setCell's code I realized that there were a couple of private method calls to make sure the workbook was ready to receive a setCell command. In our context, this validation would only need to happen once, before we even loop over the query.

WIth this new logic in mind, I removed the reference to the setCell method from setCellsFromQuery and decided to set the cells' values directly. Running the method again I managed to reduce processing time by 75%!

Total processing time setting the cells' values directly from setCellsFromQuery: 30 seconds.

This is already much better, however I still wasn't satisfied. I placed a couple of timers around my methods, trying to identify which portions were slowing down the processing. After a couple of refreshes and outputs, I managed to find the culprits. When looping over my query and columns, I made calls to the private __getRow and __getCell methods. These methods simply return a reference to the HSSFRow and HSSFCell classes. The reason why they have their own methods is that you need to check if the row or cell exists before returning it and I didn't want to have to check for the same logic every time I needed a reference to a row or cell. However, these calls were taking much more time than I would've liked, so I copied the logic out back into my setCellsFromQuery method. After this new modification, processing time was even further reduced!

Total processing time without referencing __getRow and __getCell: 8-9 seconds.

This last improvement left me baffled... I was using the exact same logic in setCellsFromQuery that I was within my private methods. Why was it suddenly taking less time? I can understand a little overhead, but shouldn't I be able to abstract logic into separate methods without sacrificing processing time? I'm really at a loss as to how I could improve this. Although I'm satisfied with the new and improved processing time (under 10 seconds for 100k+ cells on my laptop is fine for me) I was kind of hoping I could encapsulate my method calls better. If there are any ideas out there I would be really happy to hear from you.

As always, the project can be checked out from the repository. Just follow the instructions on the project's page. In the meantime, here are two snapshots of the setCellsFromQuery method.

Calling __getRow and __getCell.

Getting the row and cell references directly.