Smartlist: Exports slowly to Excel - Part 1
In the last few weeks (here and here), I've enumerated several questions that I've been asked and have seen posted to the public Dynamics GP newsgroups. These questions are typically of the "Why doesn't Smartlist..?" variety.
In this post and a few planned future posts, I'll look at each of these questions individually and give a few thoughts around them and maybe a few solutions as well.
For this post, the question is:
Why does Smartlist export to Excel so slowly?
Because Smartlist exports row by row and sets each cell individually. While that doesn't sound like a "performance based" solution, it is really what Excel itself is doing if you fill the Excel grid when you sum a row or other calculation. And that is fast. The difference is that Dexterity makes a single call to write to the cell that has to pass through the COM layer to Excel. And the COM layer is the part that is slow.
As I indicated in my premise of common questions, Smartlist export performance has been an ongoing concern for customers.
A gentleman asked in a newsgroup post around January 2007 about export performance and correctly guessed that the export routine used cell by cell. He pointed me to Microsoft KB 247412 that talks about methods to interface with Excel. Excel automation was discussed and "cell by cell" was mentioned but discouraged due to performance reasons. But one method that I recall was specifically - "Create a recordset and then have Excel use the recordset as a datasource".
That got me to thinking of how I could potentially leverage ADO in Dexterity for this recordset and find a way to make Excel use that recordset.
To make a long story short, I was able to make that work in 9.0 right before Dynamics 10.0 was released. After proving my theory, I lost interest in the project and somewhat forgot about it except from time to time when I saw an Excel export performance posting. Not knowing exactly what to do with my creation, I didn't do anything.
Not all that long ago, one of the system support techs had a customer who also was unhappy about export performance. I mentioned about my app that I'd created and how it did help this issue. As I explained how it worked and the gains provided, I had opened the Dynamics GP 10.0 Smartlist code to show him how Smartlist did the export.
And when I did, I found something unexpected that didn't exist previously.
Smartlist is looking for a Dex.ini switch and if it finds it, it calls a different Excel export routine than the one you all are familiar with.
In this routine, it loops through the rows and cells of the listview (where the data is displayed) in the same way that the normal export routine does. But instead of setting each Excel cell with that data, it writes it into a tab delimited file in your temp folder. The file is SL<userid>.tmp and it is deleted after the export is finished.
After creating the text file, the routine adds a QueryTable to this report using the text file just created. It refreshes that QueryTable and Excel populates.
How much of a difference does this change make?
In my unofficial testing of an unofficial feature for Smartlist in Dynamics 10.0, I selected the default Account Transactions Smartlist and changed the number of results from 1,000 to 10,000 to get a decent amount of data to work with.
I pressed the Export to Excel button and Smartlist exported the 10,000 rows by 7 columns in 1 minute, 55 seconds (115 seconds).
Then I added the Dex.ini switch:
and saved the dex.ini file.
Since this switch is read on the fly, I didn't have to restart Dynamics or even refresh the Smartlist. I just pushed the Export to Excel button again. How long was the export this time? Would you believe 9 seconds?
Looking at both of the Excel spreadsheets created, I couldn't see any obvious differences in formatting. And perhaps in this Smartlist that was the case since there was no QTY's or MC information displayed.
So if this is so great (and it sure seems to be based on a few tests that I've done), why is this undocumented? Why isn't it out-of-the-box functionality since the performance is awesome?
That is hard to say - most likely because it wasn't fully tested. The other thought is that because of how the data is exported we possibly lose formatting capability to Excel and so the out-of-the-box export won't always match how the export looks with this method. And if we cannot guarantee that the formats are 100% the same as they used to be, then to not break anybody's report/routine/whatever we left it since it does work - just takes longer.
So what does a Dynamics 9.0 or earlier customer do since this undocumented feature was added in 10.0? Well, hopefully they'll update to GP 2010 soon. But until then, remember my project? Stay tuned for Part 2.
Note: As an undocumented ini switch, this falls under "unsupported". So if your export isn't working or doesn't format things the same way it used to, you are on your own. You'll have to go back to the "old" method.