A few times a year I have to run a job to generate some letters using Microsoft Word’s mail merge functionality. Basically I have a letter template in Word, data source is an Excel spreadsheet, and output is a PDF file. Job size can be anywhere from a couple thousand to around 50,000 letters.
Normally a small job will take a minute or two, larger jobs may take 20-30 minutes. However, lately it is taking forever. And the longer it runs, the slower it goes. A thousand letters takes an hour. Two thousand letters may take 4 hours.
At first I suspected that Adobe Acrobat was the bottleneck, because a few months back corporate IT forced me to upgrade from Acrobat Pro XI to 2017. But I persuaded them to reinstall the old version and it is still slow. So now I suspect Word is the problem. (One bit of evidence in support of this theory – if I do a mail merge to a Word file rather than PDF, it is just as slow.)
Currently running Windows 7 with Office 2013. IT thinks upgrading to Windows 10 with Office 2016 might fix it, but who knows? Meantime, I’d like to know why something that has worked just fine for years now suddenly runs like crap. Are there any other Word mail merge users out there, have you run into this problem, and were you able to solve it?
Has your dataset grown over time? Do you have more input records?
I don’t know if it’s specifically true in your case, but a lot of computer systems have a “cliff” – a number of data items the system can handle before which performance drops noticeably and distinctly. Often based on memory and storage availability; for instance, if you can fit “X” records into memory but “X+1” records forces the system into memory swapping, drastically reducing throughput.
Just speculating, I’m not aware of any limitation of any specific component in your production line, but just pointing out the potential for this to be a full-system meta-problem based on the size of your input file.
We’ve seen plenty of unpleasant consequences as a result of the Meltdown and Specter exploit patches. I wouldn’t be surprised if the patches have slowed down mail merging, but what you’re describing is such a significant drop off in performance that I don’t know if patches are the only culprit. If you’re running the merge off a network share, try running it locally to see if there is any difference in speed.
No, the data sets are roughly the same size they have always been.
Nope, running it locally. I, too, suspect one of the Microsoft patches to be the culprit, since nothing else has changed significantly. Also, I had a coworker try to run the job on their PC and they got the same results as I did, which tells me it is not something wacky only on my PC.
The thing is, you’d think if a Microsoft patch was to blame, I’d be getting a lot more Google hits on this problem than I’ve been able to find so far. Either that or there just aren’t that many people using Word’s mail merge function.
To isolate the problem, try removing Excel from the system-- extract the data from Excel to another file format, and use that as your input source for the mail merge.
Also, it sounds like you are selecting only a few of the records from a real big spreadsheet – like 1000 letters out of 50,000 rows in the spreadsheet. Are you using the selection mechanism within Word mail merge to do this? That can be slow. Try extracting the 1000 records you wnt from the spreadsheet into a smaller temporary spreadsheet, and then using that as the input source to the mail merge.
Huh, I hadn’t even thought about Excel being the problem. I’ll give that a shot.
I am not selecting rows out of a spreadsheet. Actually I’m selecting my data from a database and saving to a text file, then importing the text file into Excel. So if I’m running 1000 letters, my spreadsheet has 1000 rows. If I’m doing 20,000 then my spreadsheet has 20,000 rows. And again, none of this is any different than how I’ve done it in the past.
For IT security reasons I don’t have direct access to the database from my workstation. So I have to query the database remotely, save to a text file, then copy the data file to my workstation and import into Excel. Yeah it’s a pain.
t-bonham, I ran a test merge this afternoon using a text file instead of Excel, and it wasn’t any faster. So I think that rules out Excel being the bottleneck.
Bumping with an update: our IT manager had suggested I consider upgrading my laptop to the current corporate standard of Windows 10 and Office 2016. His opinion was that something in Office 2013 got broken at some point, probably from a patch or update.
I had some downtime this week so I had them go ahead and update the laptop to Windows 10 and Office 2016. After getting my new and improved laptop back, I ran a test to see how my letter generation job did. It cranked out about 1200 letters in under two and a half minutes. Much better than the hour+ that it was taking a few months ago, and pretty much back to normal.
Make sure your data is being imported to and exported from Excel as a comma Seperations variable file with no formatting. In my experience word and Excel do both play well together.
As I recall, there were two protocols for doing Word/Excel mail merge. DDE, and the other, which I can’t name. The obvious difference was that things like currency and % came through differently/ were formatted differently. And they did run at different speeds. So my theory, (after the “cliff” theory was rejected) would have been that when you set up a new mail merge, you didn’t use the old (possibly inherited/retained) method you had used before. And my next (third) theory would have been that the DDE channel had slowed down because it had been subject to some new security patch.
Assuming that the current version of word still supports both methods, you’re welcome to try setting up your merge again, to run using the other method, to see if you can get it to run slow again
