• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

Word Automation - Performance Problem

Hello, Experts,

I have just begun creating a report in Microsoft Word (2000, yes I know it's old) from data held in a VB .Net application (v2005, also becoming dated).

Things were going well until I started the second page of the report.  The information here is formatted as a table, and I am populating it row-by-row with a loop like:

        For intColumn As Integer = 1 To intColumns
            P2TableRow.Cells.Item(intColumn).Range.Text = objaValues(intColumn - 1).ToString
        Next intColumn

After inserting a performance counter, I find that it is taking greater than 150 msec to populate each cell.  Formatting statements such as:

            P2TableRow.Cells.Item(intColumn).VerticalAlignment = ...
            P2TableRow.Cells.Item(intColumn).Range.Bold = ...
            P2TableRow.Borders.Item(Word.WdBorderType.wdBorderTop).LineWidth = ...

also take an inordinate length of time to execute.

Can anyone offer suggestions as to what might be causing this poor performance, or how to populate the table more efficiently.

1 Solution
Jacques Bourgeois (James Burger)PresidentCommented:
A lot of things.

Everytime you have a dot (.) in a command, you have a communication from your .NET application to the Interop to Ole to Word and back the reverse way. A lot of intermediaries.

In P2TableRow.Cells.Item(intColumn).VerticalAlignment, that is 3 calls (3 dots), that go through 3 dll/applications twice (back and forth). 18 communications between different components. In some cases, conversions need to be done because the format of some types of data (such as dates) is not the same in .NET as it is in COM (the standard running under Word).

There are many ways to go around that.

The best one is to have a VBA macro in the document or the template used to create the document, and call the macro from .NET, passing it the necessary parameters. Instead of having 20 lines that all start the back and forth dialog, you have only one.

<WordApplication>.Run("<MacroName>", "foo", True, False, 23, 3, True, vbRed)

Instead of passing each String individually as I do here, you could also pass them in one shot by having the first parameter of the macro as an array.

There is but one call through the components, most of the job is done inside of Word with almost no back and forth communications.

Another one is the With construct, that enables .NET to grab a pointer to the object you are working with and call it directly instead of having to do a few back and forth trips just to know where the object is in memory.

P2TableRow.Cells.Item(intColumn).VerticalAlignment = ...
P2TableRow.Cells.Item(intColumn).Range.Bold = ...

could be written

With P2TableRow.Cells.Item(intColumn)
   .VerticalAlignment = ...
   With .Range
      .Text = ...
      .Bold = ...
      .Italic = ...
      .FontSize = ...
   End With
End With

This may make a big difference. It always did in VB6/VBA. In .NET, because the compiler does a better optimization job, sometimes you do not feel a difference, because the compiler might have put a With even if you did not. But it is worth a try.

The idea, you probably got it, is to try to limit as much as possible the number of times there is a call between your application and Word.

Another trick that changes things a lot is to make Word invisible and/or turn of ScreenUpdating.

<WordApplication>.Visible = True
<WordApplication>.ScreenUpdating = False

Not interesting in debugging because you do not see what is happening, but in real life, since word does not have to care about refreshing the screen ant take care of the margins, page breaks, pagination and all the likes, you can get a great boost of performance. Tablea are specially sensitive to that, because of the need to mix text with graphics. I got a 20x increase in speed in one of my applications that generated a 32 pages table once, just by turning off ScreenUpdating.

Just be sure to turn everyting back to True in case of an Exception and after the job is done.

omegaomegaDeveloperAuthor Commented:
Hello, JamesBurger,

Many thanks.  Those all sound like very good suggestions.  The Word application is already hidden, but I had not turned off ScreenUpdating.  I had hoped to avoid creating a template for this report.  My reluctance was just to avoid having to change the installation application.  But if moving the work to a Word macro solves the performance problem a template is certainly an option.  (This would also allow me to pre-format my tables.)  

I will give your suggestions a try and report back next week on my progress.  Thanks again.

You can try to set the formatting on the table overall rather than at the cell level.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Using com interop is very computing expensive. Also, can't be used in server side.
If you can output word 203 or above as final documents, I really recommend using xslt transforms to generate it. It's really fast, and as it's template based, you don't need to recompile your application to make future changes on the document.
Find an example here.
Best regards.
omegaomegaDeveloperAuthor Commented:
Hello, JamesBurger et al,

Thanks for all your input.   I have now had a chance to try some of the suggestions.

I repeated tests a few times get an impression of the effect of changes.  Although I tried to keep the test system otherwise stable I still found big differences in timing from one test to the next.  (It was taking between 35 and 65 seconds to create/fill a table with five columns and 35 rows.)  Even so, I
was able to get a rough idea of the benefits of the suggestions.  

By applying the suggestions such as turning off screen updating, eliminating unnecessary repetition of "dots" and minimizing the total number of calls made, I was able to reduce the time by about 50%.  This wasn't nearly enough to make the generation of a large number of tables feasible.  

So I finally tried JamesBurger's "best one" -- i.e. use a VBA macro in a template used to create the document.  I also followed his suggestion and passed in all the table data in an array, so only one call was required.  With this change, I was able to fill the table in approximately 0.3 seconds.

This two order-of-magnitude improvement solves the problem for me, so special thanks to JamesBurger for that excellent idea.  




(though not enough to claim great statistical significance).  My tests
By reducing the number of "dots" in my
omegaomegaDeveloperAuthor Commented:
Many thanks for your suggestions.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now