Web API performance issues with large dynamic XML

Question

I’ve created an WebAPI sql interface so that development users can dynamically query a database through a RESTful interface. It’s fast for small queries but when larger ones come into play there are serious lag problems especially when multiple simultaneous calls are made. I’ve run performance analysis on this and overwhelming majority of the processing is done on the line:

var xe = reader.ReadOuterXml();

The clients are expecting XML text strings so I’ve tried to just do an ExecuteReader but then the results are returned in 2033 byte chunks which seems to be slower which is unfortunate because running this query in SQL Server Management Studio (with 'For XML Auto') is almost instant.

I’m testing with 16MB XML responses but their responses are upwards of 150MB - 200MB.

How can I optimize this to handle these large responses better? The code is below:

public XElement AdHockSelect([FromBody] XElement valueElement, string connectionStringName, string database)
{
    try
    {
        string rawConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        string sqlconnectionstring = string.Format(rawConnectionString, database);
        using (SqlConnection sqlConnection = new SqlConnection(sqlconnectionstring))
        {
            string[] sqlComandTexts = valueElement.Value.Trim().Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
            string sqlQueryText = "";

            foreach (var sqlComandText in sqlComandTexts)
            {
                sqlQueryText += sqlComandText + "  for xml auto;";
            }

            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand(sqlQueryText, sqlConnection);

            using (XmlReader reader = sqlCommand.ExecuteXmlReader())
            {
                XElement xd = new XElement(new XElement("Response"));

                if (reader.EOF)
                {
                    return XElement.Parse("<Response/>");
                }
                else
                {
                    while (!reader.EOF)
                    {
                        if (reader.NodeType == XmlNodeType.Element)
                        {
                            var xe = reader.ReadOuterXml();
                            xd.Add(XElement.Parse(xe));
                        }
                        else
                        {
                            reader.Read();
                        }
                    }
                    return xd;
                }
            }
        }
    }

Do your profiles suggest what is taking so much time under `reader.ReadOuterXml()`? — dbc, Mar 11 '15 at 18:27
More specifically, is `ReadOuterXml()` taking a long time because each top-level element is very large so the string(s) returned aer very large? — dbc, Mar 11 '15 at 18:32
What is the purpose of the code inside the `using (XmlReader reader = sqlCommand.ExecuteXmlReader())` statement? It looks as though it creates an `XElement` named `"Response"` and then loads the XML under it. Since a well-formed XML document only has one root element, the `Response` node will only have one child. So, is the `while` loop necessary? — dbc, Mar 11 '15 at 19:12
@dbc Most of the time is either taken up by that line (46%) or System.Xml.Linq.ni.dll (51.9%) Also I'm looping through the results in case the service receives multiple sql statements. — Maleki, Mar 11 '15 at 20:04

dbc · Accepted Answer · 2015-03-11T18:55:48.510

Rather than fully loading a string representation of each element, consider using XNode.ReadFrom() to directly stream the XML into a new XElement:

                if (reader.NodeType == XmlNodeType.Element)
                {
                    var element = XNode.ReadFrom(reader) as XElement;
                    if (element != null)
                        xd.Add(element);
                }
                else
                {
                    reader.Read();
                }

This will be simpler than using ReadOuterXml(), which uses a temporary XmlWriter to copy the XML in the input stream to an output StringWriter. But I can't predict whether the improvement will be substantial without knowing something about where your time is being spent under ReadOuterXml().

That increased performance by about 40% in my tests. Thanks! — Maleki, Mar 12 '15 at 14:15

Web API performance issues with large dynamic XML

1 Answers1

Linked