Pivot Table performance with large datasets

Oct 14, 2013 at 3:59 PM
I see in some of the blog posts that the way sheet data was created was modified to improve performance by using the memory spreadsheet. I have about 16000 rows in my dataset and it creates those in less than 5 seconds so job well done. The problem I have is I need to create a pivot table on that dataset and its taking about 20 minutes. Any chance the pivot table can get the same performance enhancement? If not any pointers/suggestions on what I could do to make that happen?
Oct 17, 2013 at 1:29 PM
Edited Oct 17, 2013 at 1:39 PM
I figured out a work around for the performance issues for pivot tables.

Set the named range for the pivot table to the first 2 rows of the worksheet data so you get the column headings and one data row.

WorksheetAccessor.SetRange(xlsDocument, "PivotTableRange", "Data", 1, 1, 2, totalColumns);

Create the pivot table with the named ranged defined in step 1.

var pivot = WorksheetAccessor.AddWorksheet(xlsDocument, "Pivot Table");
WorksheetAccessor.CreatePivotTable(xlsDocument, "PivotTableRange", pivot);

Right before you save the spreadsheet (or in my case right before I write out the contents of the memory spreadsheet to Response.OutputStream), update the named range for the pivot table to set the end row to the actual end row number in the worksheet data.

WorksheetAccessor.UpdateRangeEndRow(xlsDocument, "PivotTableRange", totalNumberOfRows);

When you open the spreadsheet you'll see that the pivot table was created with all the rows from the source data work sheet. This work around does not seem to make opening the spreadsheet any slower and it eliminates the 20 minutes it was taking to create the pivot table when I gave it a 16000 row range. It will prompt you to save your spreadsheet when you close it which I'm guessing is happening because it needs to update the xml in the spreadsheet document with the actual number of rows but the save happens quickly so not really a big deal.