locked
Excel Pivot OptimizeCache RRS feed

  • Question

  • This sounds like something that would make performance better. I can't find any documentation on what it actually does. I have some really large Pivottables. If I can improve the performance I would like to.

    Below  is my code

    The optimize fails. I have tried moving it after the data gets added, but nothing works.

    Removing the .OptimizeCache and the code runs clean.

                Dim objPivotCache As Excel.PivotCache
                
                objPivotCache = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlExternal, System.Reflection.Missing.Value, Excel.XlPivotTableVersionList.xlPivotTableVersion14)
                objPivotCache.OptimizeCache = True

               With objPivotCache
                    .SavePassword = False
                    '.BackgroundQuery = True

                    .Recordset = rstb

                    '  On Error Resume Next
                    .CreatePivotTable(TableDestination:=os, TableName:=tqtname)

               End With    
    • Edited by AuroraMan1 Saturday, March 5, 2016 11:51 PM
    Saturday, March 5, 2016 11:49 PM

All replies

  • Hi AuroraMan1,

    Based on the link below, Cache optimization results in additional queries and degrades initial performance of the PivotTable report.

    # PivotCache.OptimizeCache property
    https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pivotcache.optimizecache.aspx?f=255&MSPPError=-2147217396

    What do you mean by “optimize fails”? Did you get any error? If you manually create Pivot Table, did you get the same issue? If not, I suggest you try to record steps which you manually operate, and check the recorded code.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 8, 2016 5:18 AM
  • Edward, Thanks for the response. I am trying to understand if there are ANY benefits to optimize cache. I have read what you provided, which does not explain what it actually does, and if there are any benefits to optimizing the cache of a pivot. I clearly is not supported for recordset, and only from locally loaded data. It seems to be the least documented thing on the internet. I am not worried about load times.

    Adam

    Wednesday, March 16, 2016 2:56 PM
  • Hi AuroraMan,

    >> I am trying to understand if there are ANY benefits to optimize cache

    When you create a PivotTable, Microsoft Excel creates a hidden copy (cache) of the source data that is used to perform aggregations and other PivotTable functions. By using this cache, Microsoft Excel can quickly calculate PivotTables and, at the same time, maintain the integrity of the original data. This cache can affect memory use, speed, and file size depending on options that you select when you create your PivotTable.

    You could refer the link below for more information.
    # Optimizing PivotTable Performance
    https://support.microsoft.com/en-us/kb/104308

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, March 17, 2016 5:11 AM
  • Edward, I am refreshing to the option (within) the cache itself.

    objPivotCache.OptimizeCache = True

    What does it do? and When can you use it. It crashes on a Recordset Pivot. It seems to imply some kinda of performance gain on fields that are small.

    Thanks

    Adam

    Monday, April 25, 2016 6:19 PM
  • Hi Adam,

    >> What does it do?

    Cache optimization results in additional queries.

    For your original issue about PivotTable performance, I suggest refer my above reply.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, April 26, 2016 3:12 AM