none
Performance issue with PivotTable created Programmatically RRS feed

  • Question

  • I am facing a really weird performance issue with PivotTable created from VSTO and VBA. 

    The pivot tables created  Programmatically takes few seconds (~15) every time I add a new field/Metric on the pivot and it then executes the query. 

    However, If i add pivot table using same connection through Data > From Other Sources (Analysis services) in Excel there is no delay, it goes to executing query directly. 

    There is a huge difference in performance. Below is the VSTO code used to add Pivot. 

                Globals.ThisAddIn.Application.ActiveWorkbook.Connections.Add("CubeConnection", "", connectionString,sheetName, 1);
                Globals.ThisAddIn.Application.ActiveWorkbook.ShowPivotChartActiveFields = true;
                Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches().Create(XlPivotTableSourceType.xlExternal, Globals.ThisAddIn.Application.ActiveWorkbook.Connections["CubeConnection"],
                                                     XlPivotTableVersionList.xlPivotTableVersion14).CreatePivotTable(string.Concat(sheetName,cellValue));
    

    Can anyone help? 

    Thanks, 

    Monday, June 10, 2013 10:17 AM

All replies

  • Hi Parth-p,

    Thank you for posting in the MSDN Forum.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day!

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, June 12, 2013 6:23 AM
    Moderator
  • Hello Parth-p,

    There will always be a difference in the performance when you add Pivot table in Excel (Data > From Other Sources (Analysis services)) and when you run the managed code (VSTO addin). As the code is managed, all the calls to Excel have to pass the interop layer.

    Does this issue happens on a single machine? Have you tried the addin on some other machine? It would be helpful if you can provide a sample so the we can reproduce the issue.

    Regards,

    Ajay

    Thursday, June 13, 2013 8:39 PM