Excel PowerPivot SQL 2012 vs. Excel 2013 Performance Demo
Posted by denglishbi on July 30, 2012
I just wanted to do a blog post to demonstrate a performance situation I ran into last week. I had created a PowerPivot model in Excel 2013 and it performed very well (quite the understatement, phenomenal) when creating PivotTables and Power View reports. I then went ahead and recreated the model in Excel 2010 with the SQL Server 2012 PowerPivot add-in. The model creation went fine, but when it came to trying to replicate the report I ran into a slight snag. Well I actually couldn’t even complete the report, I ran out of memory on a box with 8GB of memory.
We decided to upgrade the box and gave it an additional 4GB of memory, but that didn’t change anything, the query still consumed all of the memory and wouldn’t complete. We decided to test this out and migrate the model to a Tabular SSAS instance on a server with 64GB of RAM. Well the query for the PivotTable just kept running and we watched the Analysis Services process climb to over 18GB and it was still climbing with no results coming back. Needless to say I was confused and a bit embarrassed. I decided to give it a go in Excel 2013 again and once again no issues and the results were very fast (might even say blazing fast).
To demonstrate this I have gone ahead and created a demo of the exact scenario with a very comparative sample so you can get a feel for what I was seeing, just take a look at this video:
In reaching out to the product team I discovered that in Office 15 there have been some performance improvements when it comes to dealing with filters. Well we can definitely see that based on my results:) It turns out that these same performance improvements will be coming eventually to the current version as well, we just need to wait for the release of SP1 for SQL Server 2012. Some of the improvements seen will also be available to querying the data in Analysis Services as well, both Tabular and Multidimensional! So just hold on until SP1 is released. If you want to checkout the performance improvements today have a go with PowerPivot for Excel 2013 (you will not be disappointed).