Slow connection to SQL Server from Microsoft Word XP

[Gerald Vogt]

I connect to a Microsoft SQL Server 8 database over the internet to
retrieve the content of a table to be put into a mail merge document
using the OLD DB providers for ODBC or SQL Server. Connection to the
database from Word is very slow though. When opening the document more
then 1.1MB of data is transferred which takes some while.

A connection using the same data source from Microsoft Access XP to
display all rows in that table only requires 110KB to be transferred and
is very quickly available. (Only when I switch to design view in Access
it takes longer and transfers much more data so my guess is that Word
retrieves the complete table description together with the content data.)

Is there a way to optimize that?

Thanks,

Gerald

 

[Peter Jamieson]

As a general rule, Word can be slow because it wants to get all the records
before it actually starts the merge, because it wants to display its
recipients dialog box and let you select/deselect individual records. I
don't think there is any way around that. However, that that doesn't explain
the additional traffic, unless for example your Word application is
transferring all the data from all the columns in the table even when you
have only requested certain columns.

Are you actually using the provider for SQL Server? Or the provider for ODBC
data sources? Or do they both behave the same way?

I would consider trying to connect via ODBC to see if the performance was
any better, but if you have any Unicode date you may experience some data
loss.

Sorry not to be able to provide a more useful reply.

 

[Gerald Vogt]

As a general rule, Word can be slow because it wants to get all the records
before it actually starts the merge, because it wants to display its
recipients dialog box and let you select/deselect individual records. I
don't think there is any way around that. However, that that doesn't explain
the additional traffic, unless for example your Word application is
transferring all the data from all the columns in the table even when you
have only requested certain columns.

Are you actually using the provider for SQL Server? Or the provider for ODBC
data sources? Or do they both behave the same way?

I tried both. Both with similar results. In part it seems to depend on
which record in the table in the mail merge controls at startup. When I
switched back and forth between ODBC and SQL the document always
selected the first record and I got these transfers:

opening the document: 309 KB
switching to the second record in the mail merge controls: 2000 KB
creating the merged document with all records: 673 KB
closing the merged document (I suppose i.e. redraw of main document): 673 KB

The actual data to be transferred including the column names is roughly
12 KB. I select and show all columns of the records so it has to be the
whole table anyway.

When I switch to the last record, save, close, and re-open the document
that amount of data transferred during opening is about 1340 KB. So it
seems in part to depend on the record selected during opening.

I used a network monitor to sniff the traffic and found that the main
reason despite retrieving all records from the database is that there
seems to be no optimization at all: records are retrieved over and over
again. Which results in the huge overhead. I believe this is related to
quite a few conditional IF fields that depend on the merge fields. These
IF fields seem to be evaluated even if you turn off the mail merge
preview. So even if preview turned off, the records are being
transferred. (it does not make a difference if I only show fields codes
or not switching with ALT-F9)

The amount of data transferred again when closing a different window
leads me to the conclusion that Word actually basically transfers
records (probably all records from 1 up to the one selected in the
controls) each time it needs some data instead of caching. Thus, for
every IF I have it gets all the data, and I have quite a few of them in
my document. Although, even in my test document in which I put only all
of my 132 columns with no further IFs the preview is terribly slow but
opening in non-preview is fast.

So, to me it looks like a caching problem. Is there a way to tell the
SQL Server Provider or the ODBC Provider to cache the records they
retrieve? That would probably do it for me.

If not I probably have to create a terribly ugly report with MS Access...

Gerald

 

[Cindy M -WordMVP-]

Hi Gerald,

All your observations correspond to those I've made over the years. The way
mail merge functions in Word hasn't been changed since it was conceived some
fifteen years ago. All we've had is changes in the UI, but the way the
connections and data are handled hasn't been altered.

So, yes, Word requeries the data source all the time; there is no caching. And
Word also has the disconcerting habit of starting over with the first record
every time it checks the data; that's why merges to many records are slower by
a geometrical rather than a linear proportion.

You might try using an AutoClose macro for merge documents that makes sure the
data view is off? And StoredProcs might be faster than linking directly to the
tables?

I tried both. Both with similar results. In part it seems to depend on
which record in the table in the mail merge controls at startup. When I
switched back and forth between ODBC and SQL the document always
selected the first record and I got these transfers:

opening the document: 309 KB
switching to the second record in the mail merge controls: 2000 KB
creating the merged document with all records: 673 KB
closing the merged document (I suppose i.e. redraw of main document): 673 KB

The actual data to be transferred including the column names is roughly
12 KB. I select and show all columns of the records so it has to be the
whole table anyway.

When I switch to the last record, save, close, and re-open the document
that amount of data transferred during opening is about 1340 KB. So it
seems in part to depend on the record selected during opening.

I used a network monitor to sniff the traffic and found that the main
reason despite retrieving all records from the database is that there
seems to be no optimization at all: records are retrieved over and over
again. Which results in the huge overhead. I believe this is related to
quite a few conditional IF fields that depend on the merge fields. These
IF fields seem to be evaluated even if you turn off the mail merge
preview. So even if preview turned off, the records are being
transferred. (it does not make a difference if I only show fields codes
or not switching with ALT-F9)

The amount of data transferred again when closing a different window
leads me to the conclusion that Word actually basically transfers
records (probably all records from 1 up to the one selected in the
controls) each time it needs some data instead of caching. Thus, for
every IF I have it gets all the data, and I have quite a few of them in
my document. Although, even in my test document in which I put only all
of my 132 columns with no further IFs the preview is terribly slow but
opening in non-preview is fast.

So, to me it looks like a caching problem. Is there a way to tell the
SQL Server Provider or the ODBC Provider to cache the records they
retrieve? That would probably do it for me.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Gerald Vogt]

You might try using an AutoClose macro for merge documents that makes sure the
data view is off? And StoredProcs might be faster than linking directly to the
tables?

For the moment, I found a workaround: I created an MS Access mdb file an
linked the table into that. In the next step, I link my mail merge
document with the table in the mdb file. This is certainly a little
detour but I have it working with reasonable speed (much faster than
before) and it even gets japanese unicode characters right. I will see
what happens once the database fills with real record sets...

Gerald