WCF Service Generating Excel File (10000 Rows) and sending it to Client (Performance Issues)

Question

In the Asp.net MVC application we have a page to download a dynamically generated excel report. The client application calls the WCF service which generates the excel file and returns the file name to the client. The WCF service is using OpenXML Sax Approach to generate the excel file.

The server calls a stored procedure and uses a data reader to get the data. Usually the file contains 10000 records. We didn't face any performance issue in test environment. In production if 10 people access the report the server memory is hitting the max and the cpu utilisation is also 98%. Because of this it creates problems for all the application in that server. The Server is having only 4GB RAM.I running 4 application. Usually my application takes more memory.

Here is the code:

public string GetMemberRosterHistoryFile(string VendorId, string versionId, DateTime FromDate, string ActionIndicator)
{
    string path = ConfigurationManager.AppSettings["FilePath"] + Guid.NewGuid() + ".xlsx";
    try
    {
        path = PathInfo.GetPath(path);
        log4net.ThreadContext.Properties["MethodName"] = "GetMemberRostersHistory";
        log.Info("Getting member rosters History");
        string sConn = ConfigurationManager.ConnectionStrings["VendorConnectContext"].ConnectionString;
        using (SqlConnection oConn = new SqlConnection(sConn))
        {
            oConn.Open();
            log.Debug("Connected");
            string sCmd = "pGetMemberRostersHistory";
            SqlCommand oCmd = new SqlCommand(sCmd, oConn);
            oCmd.CommandTimeout = Int32.MaxValue;
            oCmd.CommandType=CommandType.StoredProcedure;
            oCmd.Parameters.AddWithValue("@FromDate", FromDate.ToShortDateString());
            oCmd.Parameters.AddWithValue("@ActionIndicator", ActionIndicator);
            int index=1;
            StringBuilder programs = new StringBuilder();
            if (string.IsNullOrEmpty(versionId))
            {
                foreach (string value in GetPrograms(VendorId))
                {
                    if (index > 1)
                    {
                        programs.Append(",");
                    }
                    programs.Append(value);
                    index++;
                }
            }
            else
            {
                foreach (string value in GetPrograms(VendorId, versionId))
                {
                    if (index > 1)
                    {
                        programs.Append(",");
                    }
                    programs.Append(value);
                    index++;
                }
            }
            oCmd.Parameters.AddWithValue("@ProgramsList", programs.ToString());

            string[] FieldNames = new string[] 
                {
                        "ActionIndicator", 
                    "ChangeNotes",
                    "ActionEffectiveDate",
                    "MembershipTerminationDate",
                    "GPOId",
                    "GLN",
                    "HIN",
                    "Name1",
                    "Name2",
                    "AddressType",
                    "Address1",
                    "Address2",
                    "Address3",
                    "City",
                    "StateProvince",
                    "ZipPostalCode",
                    "Country",
                    "PhoneNbr",
                    "FaxNbr",
                    "RelationshipToGPO",
                    "RelationshipToDirectParent",
                    "DirectParentGPOId",
                    "DirectParentName1",
                    "TopParentGPOId",
                    "TopParentName1",
                    "MemberStatus",
                    "MembershipStartDate",
                    "OrganizationalStatus",
                    "ClassOfTradeName",
                    "DEA",
                    "DSHorHRSA",
                    "PHEffectiveDate",
                    "PHExpirationDate",
                    "BLPHEffectiveDate",
                    "BLPHExpirationDate",
                    "MMEffectiveDate",
                    "MMExpirationDate",
                    "BLMMEffectiveDate",
                    "BLMMExpirationDate",
                    "DIEffectiveDate",
                    "DIExpirationDate",
                    "LBEffectiveDate",
                    "LBExpirationDate",
                    "NMEffectiveDate",
                    "NMExpirationDate"
                    ,"BLMemberId"
                        ,"GPOCorporateGroup"
                        ,"GPOAffiliateGroup"
                        ,"GPO2AffiliateGroup"
                        ,"GPORelatedGroup"
                        ,"GPOIDNGroup"

                };
        string[] columnNames = new string[] 
                    {
                        "Action Indicator",
                        "Change Notes",
                        "Action Effective Date",
                        "Membership Termination Date",
                            "GPO ID",
                            "GLN",
                            "Health Industry Number (HIN)",
                            "Name 1",
                            "Name 2",
                            "Address Type",
                            "Address 1",
                            "Address 2",
                            "Address 3",
                            "City",
                            "State/Province",
                            "Postal Code",
                            "Country",
                            "Phone",
                            "Fax",
                            "Relationship to GPO",
                            "Relationship to Direct Parent",
                            "Direct Parent GPO ID",
                            "Direct Parent Name 1",
                            "Top Parent GPO ID",
                            "Top Parent Name 1",
                            "Member Status",
                            "Membership Start Date",
                            "Organizational Status",
                            "Class of Trade",
                            "DEA #",
                            "DSH and/or HRSA Number",
                            "Pharmacy Start Date",
                            "Pharmacy End Date",
                            "BL Pharmacy Start Date",
                            "BL Pharmacy End Date",
                            "Med Surg Start Date",
                            "Med Surg End Date",
                            "BL Med Surg Start Date",
                            "BL Med Surg End Date",
                            "Food Service Start Date",
                            "Food Service End Date",
                            "Laboratory Start Date",
                            "Laboratory End Date",
                            "NonMedical Start Date",
                            "NonMedical End Date"
                            ,"Broadlane ID"
                            ,"Corporate Group"
                            ,"Affiliate Group"
                            ,"2nd Affiliate Group"
                            ,"Related Group"
                        ,"IDN Group"
                    };
            //object result = oCmd.ExecuteScalar();
            //int count=(result!=null ? (int)result : 0);
            //oCmd.CommandText = "pGetMemberRostersHistory";
            //oCmd.CommandTimeout = Int32.MaxValue;
            using (SqlDataReader oReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                SAXExcelExporter exporter = new SAXExcelExporter();
                exporter.Export(oReader, columnNames, FieldNames, path, "MemberRoster");

            }
        }
        return path;
    }
    catch (Exception ex)
    {
        log.Error("In exception", ex);
        return null;
    }
}

Export Code:

public void Export(SqlDataReader export, string[] columnNames, string[] fieldNames, string filename, string sheetName)
{
    Assembly _assembly = Assembly.GetExecutingAssembly();
    Stream stream = _assembly.GetManifestResourceStream("MA.VMS.Server.Template.xlsx");
    FileStream newfile = new FileStream(filename, FileMode.Create, FileAccess.ReadWrite);
    stream.CopyTo(newfile);
    stream.Close();
    newfile.Close();
    using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
    {
        WorkbookPart workbookPart = myDoc.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();
        string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);

        WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
        string replacementPartId = workbookPart.GetIdOfPart(replacementPart);

        OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
        OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);

        while (reader.Read())
        {
            if (reader.ElementType == typeof(SheetData))
            {
                if (reader.IsEndElement)
                    continue;
                writer.WriteStartElement(new SheetData());
    Row hr = new Row();
                writer.WriteStartElement(hr);

                for (int col = 0; col < columnNames.Length; col++)
                {
                    Cell c = new Cell();
                    c.DataType = CellValues.InlineString;
                    InlineString iss = new InlineString();
                    iss.AppendChild(new Text() { Text = columnNames[col] });
                    c.AppendChild(iss);
                    writer.WriteElement(c);
                }
    writer.WriteEndElement();

                //for (int row = -1; row < count; row++)
                while (export.Read())
                {

                    Row r = new Row();
                    writer.WriteStartElement(r);
                    //if (row == -1)
                    //{
                    //    for (int col = 0; col < columnNames.Length; col++)
                    //    {
                    //        Cell c = new Cell();
                    //        c.DataType = CellValues.InlineString;
                    //        InlineString iss = new InlineString();
                    //        iss.AppendChild(new Text() { Text = columnNames[col] });
                    //        c.AppendChild(iss);
                    //        writer.WriteElement(c);
                    //    }
                    //}
                    //else
                    //{
                        //export.Read();
                        for (int col = 0; col < fieldNames.Length; col++)
                        {
                            Cell c = new Cell();
                            c.DataType = CellValues.InlineString;
                            InlineString iss = new InlineString();
                            iss.AppendChild(new Text() { Text = GetValue(export[fieldNames[col]]) });

                            c.AppendChild(iss);
                            writer.WriteElement(c);
                        }
                    //}
                    writer.WriteEndElement();

                }

                writer.WriteEndElement();
            }
            else
            {
                if (reader.IsStartElement)
                {
                    writer.WriteStartElement(reader);
                }
                else if (reader.IsEndElement)
                {
                    writer.WriteEndElement();
                }
            }
        }

        reader.Close();
        writer.Close();

        Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
        sheet.Id.Value = replacementPartId;
        workbookPart.DeletePart(worksheetPart);

    }
}

I am worried. When I look at the proc returns the data in 26 seconds and the excel download takes more than 3 minutes.

What should i do about this scenario? Here are the solutions i am thinking:

1. Move the excel download async and send a link to download
2. Deploying the 2 applications in different server.
3. Doing memory profiler on the server

Answer 1

What serializer are you using for WCF, by default you are probably serializing in XML. I was lead in a similar project where we were sending lots of data to clients using Microsoft Sync Services, this contained lots of Xml Data which caused serialization and deserialization to be really slow on the server and client. This is because any xml contained within the entities being returned has to be escaped on the server and then unescaped on the client which was really slow with large data chunks.

To get round this we used the binary serializer BUT this is not just a server side fix, you need to change the client as well. This assumes that the only clients that connect are your .Net clients.

If you are returning a string the WCF response might look like this:

<GetMemberRosterHistoryFile>
    <MethodParameters>
        <String>
            &lt;WordDocXml&gt;
                &lt;SomeXmlElement someAttribute=&quot;foo&quot; /&gt;
            &lt;/WordDoxXml&gt;
        </String>
    </MethodParameters>
</GetMemberRosterHistoryFile>

I have used html encoding in my example which is what I think WCF does but regardless it illustrates the point. Imagine if .Net has to do this for a large document.

WCF Xml vs Binary Serialization Pros and Cons

Serialize WCF message in a binary way, and not as a SOAP Message

Answer 2

The problem is probably the mixed use of Open XML SDK classes and the SAX method classes (OpenXmlWriter in particular). The SDK have a lot of DOM baggage in them, which is why they're slower.

For this particular case, it's the Cell class. The entire Worksheet, SheetData and Row SDK classes were written out with OpenXmlWriter, but the Cell class still use the SDK version of populating data. This is the bottleneck. Try this:

List<OpenXmlAttribute> oxa;
for (int col = 0; col < fieldNames.Length; col++)
{
    oxa = new List<OpenXmlAttribute>();
    // this is the data type ("t"), with CellValues.String ("str")
    oxa.Add(new OpenXmlAttribute("t", null, "str"));

    // it's suggested you also have the cell reference, but
    // you'll have to calculate the correct cell reference yourself.
    // Here's an example:
    //oxa.Add(new OpenXmlAttribute("r", null, "A1"));

    writer.WriteStartElement(new Cell(), oxa);

    writer.WriteElement(new CellValue(GetValue(export[fieldNames[col]])));

    // this is for Cell
    writer.WriteEndElement();
}

Also, the CellValues.InlineString is for inline rich text. If you only have plain text, the CellValues.String enum value is simpler.

I also wrote an article about it some time ago. You can find out more here.

Answer 3

Those are all good ideas. However, I would look more into performance tuning.

You say you have 4GB of memory. Let's say 1GB is lost to all the overhead, and the other applications on the server are kind of idle. So that leaves 3GB to do your thing for 10 reports. That's 300MB each. Is your report around that size? Let's say your output is 100MB, then you'll easily get to 300MB with various internal representations that you should cut out. But if you're only generating 10MB, then you have some serious performance issues you need to work out.

You say that this is in production already. So a quick band-aid solution is to get this application its own server(s) with a lot of RAM. This should buy you a little while for you to track down why it's taking so much memory.

For a longer term solution, I would, like you suggested, make this an asynchronous process. I think anything more than a few seconds should be asynchronous. The client will make a request for the report, and your front end will put this request in a queue. Some background worker will pick up this request from the queue and process it. When it's done, the client can come back and download it.

This will give you better control over how many simultaneous requests there are. You will want to throttle this so as to not run out of RAM, or overburden your database. Sure your customers will be impatient, but that's better than your own system grinding to a halt while your applications are thrashing for memory.