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;
                }
            }
        }
    }

Answer 1

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().