Excel 2013 Performance Issue

Hi,

I have noticed that password protecting a workbook and/or a worksheet from within VBA runs over  two thousand times slower in Excel 2013 than it does it Excel 2010.

Consider this simple code snippet

Sub Test()
    Dim i As Integer, max As Integer, tStart As Single
    max = 100
    tStart = Timer
    For i = 1 To max
        ActiveWorkbook.Worksheets(1).Protect Password:="pword"
        ActiveWorkbook.Protect Password:="pword"
        ActiveWorkbook.Unprotect Password:="pword"
        ActiveWorkbook.Worksheets(1).Unprotect Password:="pword"
    Next
    Debug.Print max & " iterations took " & Timer - tStart & " seconds."
End Sub

This takes 142 seconds in Excel 2013 and 0.06 seconds on the same computer using Excel 2010.

The same code without the passwords takes just 0.15 seconds in Excel 2013 and 0.05 seconds in Excel 2010. A factor of 3.

I am using Excel 2013 version 15.0.4420.1017 which as far as I can tell is the most up to date.

Has anyone else experienced this type of performance issue and found a fix for it?

Thanks in advance

Rob

Maybe they used a stronger encryption algorithm. Also, your code doesn't make sense.

It's not a stronger encryption algorithm - it's better a better key derivation algorithm.

Basically what's going on is that people suck as suggesting good passwords, and consequently an attacker will usually be able to guess your password for a file within a couple of billion guesses for good passwords and a couple of million for less good ones.

For an attacker, the length of time each guess takes is now really important. Whereas a user spends most of his time using Excel, an attacker spends most of his time trying to decrypt the first block of your file to see if it's been decrypted. This means that upping the length of time to derive a key from a password has a disproportionately negative effect on an attacker compared with on a user.

For example, if Microsoft choose a key derivation algorithm that takes, say, half a second (up from a millionth of a second) to turn a password attempt into a key to test, then the startup time for Excel goes from say 10 seconds to 10.5 seconds - an overall trivial inconvienience to the user.

Now let's say that your password is good enough that it takes 10 billion guesses (say a seven letter "good" password). It used to take an attacker 2 and a half minutes to brute force to get in; it now takes 158 years.

Effectively what you're seeing is this is practice. Microsoft is upping the strength of the key derivation algorithm to help protect your file from having the password brute forced by attackers.

What's perhaps slightly less obvious from your example, is that this has been happening since Excel 2003. Each revision ups the key derevation complexity in order to "catch up" with technology making longer lengths more acceptable to users (who have better hardware) whilst still keeping brute force out of reach of attackers, who also have markedly better hardware than when previous versions of Excel were released.

So @robwellis, what is the use case for having to unprotect (or attempt to unprotect) a workbook so often?

@cbae Of course my code doesn't actually look like this but we have complex workbooks with many protected sheets. There is a time when the code unprotects the workbook and then all of the worksheets, manipulates them and then reprotects them. There is a huge performance issue between Excel 2010 and Excel 2013 which is all down to the protection issue.

@ZippyV "Doesn't make sense" Perhaps not in the isolated example, that was just to illustrate the performance issue. I thought I'd keep the post simple so people could understand it

I agree with @evildictator but they've really messed up on this one. I have found other people moaning about this but no resolution as yet.

Anyway, I have had a direct response from Microsoft to say they are aware of this issue and are currently researching the problem.

@robwellis: Did it do the same thing in the preview release?

@kettch:yes, it did. Why?

@robwellis:

I guess you just to have unprotect all before the entire code is ran and protect afterward instead of doing it per method call because I think that's more likely what you were doing.

1 day ago, robwellis wrote

I agree with @evildictator but they've really messed up on this one. I have found other people moaning about this but no resolution as yet.

Sadly this one's going to come back as a "won't fix" from the team. This behaviour is by design.

A better question is why your program needs to act in this way (other than for the obvious reason that it did it that way before).

1. Do your workbooks really need to be protected (given that you're unprotecting them anyway, which kind of makes the protection redundant).

2. Do you need to unprotect the workbook to use it?

3. Are you doing the processing on the user's machine? If so, consider offloading the work onto servers. If not, consider upgrading the servers to cope with the load.

4. You will probably find that parallelising the work makes a big difference, and that the cheapest method of parallelism is to run your program N times simultaneously. Each program can pick a file out of the "encrypted files" folder, move it to the "in use" folder and then unprotect, work and reprotect the file. It can then move the final file to the "done" folder. Now run the program 40 times and leave them running and you'll very quickly chew through the files.

@robwellis: Nothing really. Just checking to make sure that you reported the problem through the proper channels during the beta period. It's been my experience that only a small percentage of people who complain about problems have actually reported them. Office even had the smiley/frowny face buttons to make it easy.

@kettch:Yes, I've reported it elsewhere and done the frowny face thing too.I've been told that it's being investigated internally but I'm not expecting it to be fixed anytime soon.

I just installed 2013 and thankfully did not remove 2010- all macros run very slowly under 2013

@robwellis:Same here.

MS: It's a shame.

The performance on Excel 2013 is very very very poor.

I'm glad to keep Excel 2010 in my machine.

I'm fell like a moron to bought MS Office 2013.

MS is doing a good job to get out of market.

@Erwin: Is your problem also related to password protecting the files? If not, it might be better to create a new thread.

@evildictaitor: My files are not password protected.

I have a lot of excel files with macros and the macros run very slowly (compared with MS Excel 2010).

Even a simple task of put a new number in a cell is slow process (after press enter of course).

When we bought a soft we couldn't think that the performance will be worst than the older.

9 minutes ago, ErwinHeisler wrote

When we bought a soft we couldn't think that the performance will be worst than the older.

Really? Sounds like you need to read this then:

http://en.wikipedia.org/wiki/Wirth's_law 

@evildictaitor: Really...

So I guess I have to maintain my:

Intel i7-3960X CPU @ 3.30GHz, 16 GB RAM, SSD SATA 3 - 480GB.

and

use EXCEL 95 on it.

Luckily my Acrobat XI, AutoCAD 2012, Alibre 2012, ... performance better than the previous version.

@ErwinHeisler: that is indeed concerning since it is a generic performance issue now.