Vidas Matelis Analysis Services Blog

I was working recently with PowerPivot 1.0 workbook and was using it on my desktop and on my laptop. I noticed that there was  big performance difference when I was refreshing my Pivot Tables between these 2 machines so I did more tests to figure out why this is happening.

Here are specs on my machines:

Laptop (about 4 years old):
OS – Windows 7 Ultimate, 32bit
Processor: Intel Core 2 Duo CPU T9300 @ 2.5Ghz
RAM: 3GB RAM
HDD: 7200RPM HDD

Desktop (about 2 years old):
OS: Windows Server 2008 R2 Enterprise SP1, 64bit
Processor: AMD Phenom 9750 Quad-Core 2.4GHz
RAM: 8GB
HDD: 5400RPM HDD

Naturally as laptop is much less powerful, I expected it to be at least a bit slower. But that was not a feeling I was getting. I noticed that my laptop was responding always faster, so I did this simple test on both machines:

• Open this workbook (about 3MB in size with 6 pivot tables and a few slicers)
• Enable trace
• Hit refresh on one of the pivot tables
• Close worksheet.

I reviewed traces and I can see that one of the slower queries run twice as fast on the laptop comparing to my desktop.

At first I could not understand what is happening, but then I remember another issue I had with some of my development servers running much slower than expected. At that time I had issue that my SQL Server table compression operations running much slower on more powerful servers and with the help of Glenn Berry I established that this was due Power Plan settings on that server. That time I had to change servers BIOS setting for CPU usage and Control Panel settings for Power Settings and that server was compressing tables twice as fast.

So I did similar tests here. I downloaded CPU-Z utility and run tests on my laptop and on my workstation and saw that both were reporting that while idle my CPU was running on partial power –  1.2GHz on my desktop and 780MHz on my laptop.

So I changed Power Plan for my laptop and desktop:

• Control Panel
• Power Options
• Changed Power Plan from “Balanced” to “High Performance”

With new settings CPU-Z reported that my CPUs were running at their top reported speed.

I re-run the same test on my laptop and did not see any performance improvements. So I set my Power Plan on the laptop back to “Balanced”, mostly because it is important for me to have longer battery life on the laptop.

On my desktop I saw 100% performance improvement for my test workbook – my queries were running twice as fast and query duration now was very close to the same query duration on my laptop. I checked power consumption (without monitor) on my desktop and it went from about 70 watts when in “Balanced” power plan to about 75 watts when in “High Performance” power plan. Clearly I was very happy with new settings and from now my desktop machine will run on “High Performance” power plan.

Conclusion

If your machine is used to run many CPU intensive tasks like PowerPivot, try to change Power Plan settings for your PC/server to “High Performance” and test impact – you might be pleasantly surprised.