none
Would like to report awful performance of the OpenXML SDK while creating an Excel spreadsheet [SOLVED] RRS feed

Answers

  • Ladies and gents, I am glad to report that the issue has been fully solved. Blame it on my learning curve as this is my first .NET application. All I had to do was apply NGEN and let it do its magic (it compiled DocumentFormat.OpenXml).

    The increased performance is awesome:


    Thursday, May 23, 2013 10:49 PM

All replies

  • Debug version, under MS VS-2010: Ok

    Release version, under MS VS-2010: Ok

    Debug version, standalone double click: Ok

    Release version, standalone double click: 8-12 times slower

    Ironically, the last one is the one I am supposed to deploy.

    Thursday, May 23, 2013 2:06 PM
  • Typically, what I would assume in this case is that its code inside the routine:

    GenerateDrawingsPart1Content. 

    That's going to be some generated code.  What I'd do to establish which generated code is use System.Diagnostics.Stopwatch to time it.  That looks something like this:

    =====

    var timer = new Stopwatch();

    timer.Start();

    Trace.WriteLine("Checkpoint 1: " + sw.Elapsed);

    =====

    Place those in about 5-8 places in the slow routine.  Run it, find two markers that are slow -- then add a .1, .2, .3 between them.  Using that approach, you should be able to nail down a small block of code that's actually responsible for it running slow.

    I suspect that in your production deployment, you're missing some assemblies.  In particular, I suspect you're missing the serialization assemblies.  I'm guessing these reside in your output bin folder when you run from Visual Studio and that whatever tool you're using for deployment isn't grabbing them automatically.

    That is, however, a guess.  Adding some additional checkpoints, in that part's code, will narrow down the actual issue.

    Thursday, May 23, 2013 8:57 PM

  • That's going to be some generated code.  What I'd do to establish which generated code is use System.Diagnostics.Stopwatch to time it.  That looks something like this:

    =====

    var timer = new Stopwatch();

    timer.Start();

    Trace.WriteLine("Checkpoint 1: " + sw.Elapsed);

    =====

    Thanks, Dave.

    See my time measuring code below.

            // Adds child parts and generates content of the specified part.
            private void CreateParts(SpreadsheetDocument document, SheetContentsPlusAttributes content)
            {
                Stopwatch total = new Stopwatch();
                Stopwatch sw = new Stopwatch();
                total.Start();
    
                sw.Restart();
                ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
                GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);
                sw.Stop();
                Console.WriteLine("GenerateExtendedFilePropertiesPart1Content took " + sw.ElapsedMilliseconds + " ms.");
    
                sw.Restart();
                WorkbookPart workbookPart1 = document.AddWorkbookPart();
                GenerateWorkbookPart1Content(workbookPart1);
                sw.Stop();
                Console.WriteLine("GenerateWorkbookPart1Content took " + sw.ElapsedMilliseconds + " ms.");
    
                sw.Restart();
                WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId3");
                GenerateWorkbookStylesPart1Content(workbookStylesPart1);
                sw.Stop();
                Console.WriteLine("GenerateWorkbookStylesPart1Content took " + sw.ElapsedMilliseconds + " ms.");
    
                sw.Restart();
                ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId2");
                GenerateThemePart1Content(themePart1);
                sw.Stop();
                Console.WriteLine("GenerateThemePart1Content took " + sw.ElapsedMilliseconds + " ms.");
    
                sw.Restart();
                WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
                GenerateWorksheetPart1Content(worksheetPart1);
                GenerateWorksheetPart1Content(worksheetPart1, content);
                sw.Stop();
                Console.WriteLine("GenerateWorksheetPart1Content took " + sw.ElapsedMilliseconds + " ms.");
    
                sw.Restart();
                DrawingsPart drawingsPart1 = worksheetPart1.AddNewPart<DrawingsPart>("rId2");
                GenerateDrawingsPart1Content(drawingsPart1);
                sw.Stop();
                Console.WriteLine("GenerateDrawingsPart1Content took " + sw.ElapsedMilliseconds + " ms.");
    
                sw.Restart();
                SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = worksheetPart1.AddNewPart<SpreadsheetPrinterSettingsPart>("rId1");
                GenerateSpreadsheetPrinterSettingsPart1Content(spreadsheetPrinterSettingsPart1);
                sw.Stop();
                Console.WriteLine("GenerateSpreadsheetPrinterSettingsPart1Content took " + sw.ElapsedMilliseconds + " ms.");
    
                sw.Restart();
                SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId4");
                GenerateSharedStringTablePart1Content(sharedStringTablePart1);
                GenerateSharedStringTablePart1Content(sharedStringTablePart1, content.rowList);
                sw.Stop();
                Console.WriteLine("GenerateSharedStringTablePart1Content took " + sw.ElapsedMilliseconds + " ms.");
    
                sw.Restart();
                SetPackageProperties(document);
                sw.Stop();
                Console.WriteLine("SetPackageProperties took " + sw.ElapsedMilliseconds + " ms.");
    
                total.Stop();
                Console.WriteLine("Total: " + total.ElapsedMilliseconds + " ms.");
            }
    

    Thursday, May 23, 2013 10:42 PM
  • Ladies and gents, I am glad to report that the issue has been fully solved. Blame it on my learning curve as this is my first .NET application. All I had to do was apply NGEN and let it do its magic (it compiled DocumentFormat.OpenXml).

    The increased performance is awesome:


    Thursday, May 23, 2013 10:49 PM