Solved

Word Automation - Performance Problem

Posted on 2011-09-30
6
443 Views
Last Modified: 2012-05-12
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.

Thanks,
Randy
0
Comment
Question by:omegaomega
6 Comments
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 36895373
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.

0
 
LVL 12

Author Comment

by:omegaomega
ID: 36895439
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.

Cheers,
Randy
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36902427
You can try to set the formatting on the table overall rather than at the cell level.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:cubaman_24
ID: 36908902
Hello:
Using com interop is very computing expensive. Also, can't be used in server side.
http://support.microsoft.com/kb/257757
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.
http://weblogs.asp.net/cnagel/archive/2004/09/25/234188.aspx
Best regards.
0
 
LVL 12

Author Comment

by:omegaomega
ID: 36913638
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.  

Cheers,
Randy


 

 

(though not enough to claim great statistical significance).  My tests
By reducing the number of "dots" in my
0
 
LVL 12

Author Closing Comment

by:omegaomega
ID: 36913643
Many thanks for your suggestions.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now