Smartlist: Exports slowly to Excel - Part 2

Patrick Roth - Click for blog homepageThis is the 4th post in a series of Smartlist posts in the last weeks.  In these articles, I've given answers and solutions to common questions about Smartlist that are typically of the "Why doesn't Smartlist..?" variety.

Last week, the question we were looking at was:

Why does Smartlist export to Excel so slowly?

The answer to that this week is the same as last week:

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.

Of course last week, I talked about an undocumented dex.ini switch that seems to solve the export performance issue for 10.0 and newer Dynamics GP customers. 

So what if you haven't made that leap yet?  Let's take a look at the project I was working on by first looking at some Excel stuff and then the method I used to solve this.

As I noted in the previous post, 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 the newsgroup to a Microsoft Knowledge Base (KB) Article, KB 247412 that talks about methods to interface with Excel. 

In the KB, the methods are:

Transfer data cell by cell

This is what we are doing now, not so good.

Transfer data to the clipboard and then paste the clipboard contents into an Excel worksheet

Dexterity doesn't really have any kind of clipboard functionality, so this is pretty much out.

Transfer data in an array to a range of cells

An idea but Dexterity doesn't allow multi dimensional arrays - so this is out.

Create a QueryTable on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data source

Not sure how this would work because we don't have a SQL Query to run.  But This is the approach the ini switch solution issues for a text file it creates.  Didn't think of that at the time.

Transfer data in an ADO recordset to a range of cells using the CopyFromRecordset method

This sounded interesting and 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.

But where to get a recordset from since all we have at this time is a listview control full of data?  Make our own!

I cannot really make this a 'sample' application but I can take the opportunity to give the technical pieces of how I made this customization and how it works.

The first part is easy, we just need a cross dictionary focus trigger on the Excel button in the Smartlist window.  Not very complex and I won't show the trigger registration script since it isn't very interesting.

Because the CopyFromRecordset method of the Excel report object needs an actual ADO recordset and we don't have one - we need to make one.  If you have a recordset that hasn't been created via a query and has no data, then the developer can add columns to the recordset.  Once the columns are entered, new records can be inserted into it.

Create an ADO recordset

local reference rs;

rs = COM_CreateObject("ADODB.Recordset");

{read column headings}
{asi_lv is rererence to the smartlist Listview control}
for j = 1 to column_count do
 {make the headings}
 sCellContents = ListView_ColumnGetLabel(field(asi_lv), j);
 worksheet.Cells(1,j).Value = sCellContents;
 
 {and also create the field values}
 call MakeADOField,
  rs,
  "Column" + str(j),
  nTypes[j],
  j, {column/array value to check}
  string_column_length; {256 element array of all the string columns max sizes. Didn't show this code.}
  
end for;

{some of procedure MakeAdoField}

inout ADODB.Recordset rs;
in string columnName;
in integer itype;
in integer index;
in integer string_size[256];

isize = string_size[index];
{not larger than 255}
isize = min(isize,255);

case itype

 in [ASI_DT_LONG,ASI_DT_INTEGER]
  rs.Fields.Append(columnName,adInteger);
  rs.Fields.Item[columnName].Attributes  = 24;
  rs.Fields.Item[columnName].DefinedSize = 4;
  rs.Fields.Item[columnName].NumericScale = 255;
  rs.Fields.Item[columnName].Precision    = 10;
 in [ASI_DT_TIME]
  rs.Fields.Append(columnName,adDBTime);
  rs.Fields.Item[columnName].Attributes  = 24;
  rs.Fields.Item[columnName].DefinedSize = 16;  
  rs.Fields.Item[columnName].NumericScale = 3;
  rs.Fields.Item[columnName].Precision    = 23;

   <other types snipped - similar to this.
    I determined these values by running an ADO query and then looking
    at the recordset columns to see the types>

else {be a string}
  rs.Fields.Append(columnName,adChar,isize);
  rs.Fields.Item[columnName].Attributes   = 24;

So after looping through the columns of the listview control and calling MakeAdoField, I now have a recordset with defined columns for the data but no actual data.

Getting the data is easy- we have to loop through the entire listview for each row & column and read it out of the field.  While you might think that is slow, it is really very quick.  Even if it wasn't, it would still have to be done to fill the recordset to send to Excel.

Loop through ListView and fill recordset

{open up recordset so we can add data to it}
rs.Open();

{walk through each row and column}
current_item = 1;
while (current_item <= item_count) do
 {new line, add new record}
 rs.AddNew();
 
    for j = 1 to column_count do
        subitem = ListView_ColumnGetSubitem(field(asi_lv), j);
 
  {manipulate the value so that the cell has the correct format in Excel depending on the cell's datatype}
  case nTypes[subitem]
   in [ASI_DT_DATE]
    dateTemp = ListView_ItemGetDateSubitem(field(asi_lv),current_item,subitem);
    if empty(dateTemp) then
     dateTemp = mkdate(1,1,1901);
    end if;    
    rs.Fields.Item["Column" + str(j)].Value = dateTemp;    
   in [ASI_DT_CURRENCY]
    rs.Fields.Item["Column" + str(j)].Value = ListView_ItemGetCurrencySubitem(field(asi_lv), current_item, subitem);
    
   <some types removed for clarity>
   
   else {string}
    rs.Fields.Item["Column" + str(j)].Value = trim(ListView_ItemGetSubitem(field(asi_lv), current_item, subitem));
  end case;     
    end for;

    {got all the columns in the current row, update the recordset to add the record}
    rs.Update();
    {update progress bar}
    call with name "ASI_UpdateExportProgress of form ASI_Explorer" in dictionary 1493, current_item, item_count;

    increment current_item;
end while;

When this while loop is finished, the recordset object rs now contains all of the data in the listview control.

That really is pretty much it, just update Excel using the worksheet reference that was created earlier (not shown) and make Excel visible.

Finish Up

{assign the worksheet range our recordset by copying it using the excel method}
worksheet.range["A2"].CopyFromRecordset(rs);

worksheet.Cells.EntireColumn.AutoFit();

app.Interactive = true;
app.Visible = true;

rs.Close();

 

Actually looking at the project now and what I have above, there really isn't too much more than this in the code.  The few bigger pieces that I omitted code for are noted below.

I omitted how I knew what the columns types were (pass through sanScript to read the ASI_ListView_Datatype table). 

I also didn't show is that before the export starts, I actually loop through the whole listview!  Why?  When the code creates the recordset, you have to tell it how large the string fields are in the column.  What I found in my testing was that even though I did an "AutoFit()" call in Excel it wasn't trimming down the unused string spaces - meaning that all my string cells were 255 characters wide.  So I loop through the whole thing and find the max size of the string fields used and store that.  Then later when I create the ADO recordset I use that max size as the size of the column.  You can see this when the progress bar pops up, I create the recordset object, create Excel, the worksheet, etc.  And then I loop through the listview to get those max sizes.  Then the progress bar starts to go and that is when the loop to fill the recordset for real happens.

Once that is done, the CopyFromRecordSet doesn't take but a second and Excel is made visible.

How much of a difference does this change make and is it better or worse than the 10.0 solution?

Well it makes a ton of difference and is a little bit worse than how the enhanced export does it.

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.

To get a baseline, 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:

SmartlistEnhancedExcelExport=TRUE

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?  A fantastic 9 seconds.

OK, we knew this from the last post.

With my customization in place I displayed the same Smartlist with the same size data as before.  Running the export, this time it took 13 seconds according to my wristwatch.  I attribute the extra 4 seconds to having to loop through the listview once to get the max string sizes.  So if I could get around that then I think the timing would be the about the same.  Not that 13 seconds is so awful...

Attached is the cnk file of the above customization (without source).  You can use it on any version of Dynamics - I've tested it on 9.0, 10.0, and 2010 without any issues.

Hopefully you all learned something from my somewhat quickly put together post.  But if not, hopefully at least the working cnk file included helps out.

Patrick
Developer Support

Note: As usual, this application is provided as-is, use at your own risk, undocumented, completely unsupported, etc etc. Feel free to add comments on issues you see with the application but don't expect that I'll fix them. If the application doesn't work how you want/expect it to - then you should consider not using it.

EDIT 08/05/11 - I fixed the issue reported by Kerry in the Comments section and replaced the SLPerf.zip file attached to this blog post with that new version.

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code and any code in attachments to this blog
// post is released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.) 

SLPerf.zip