c# SqlDataReader to XElement Efficiently

Question

I have a Sql SP that takes around 1 minute to run, returning 25,000 rows of data. (multiple datasets can be returned).

Currently trying to convert this into an XElement/XDocument to produce several reports results in the c# method converting this taking over 30 minutes, which requires a Sql Connection\Command Timeout of 30 minutes, which is just far far too long.

Can anyone help troubleshoot/find where i can make improvements to the following conversion code, as there must be a huge inefficiency in here somewhere.

The Call

public void xyzCall()
{
  ....
  XElement result = SqlDataReadertoXML(sqlcommand.ExecuteReader());
  ....
}

The Conversion Function

    private XElement SqlDataReadertoXML(SqlDataReader datareader)
    {
            XElement results = new XElement("ResultSets");

            // Read Next RecordSet
            do
            {
                XElement result = new XElement("ResultSet");
                //Read Next Row in this RecordSet
                while (datareader.Read())
                {
                    XElement datanode = new XElement("Item");

                    // Read Each Column in this RecordSet
                    for (int i = 0; i < datareader.FieldCount; i++)
                    {
                        // Node.Attr("Name") = Column Name, Node.Value = Field
                        if (datareader.GetName(i) != "") datanode.Add(new XElement(datareader.GetName(i), datareader[i].ToString()));
                    }
                    result.Add(datanode);
                }
                results.Add(new XElement(result));
            } while (datareader.NextResult());

            datareader.Close();
            return results;
    }

How sure are you that the LINQ to XML is the bottleneck? If you take out the LINQ to XML bits, but still fetch all the data (still call `datareader.GetName(i)` and `datareader[i].ToString()`) how long does it take? — Jon Skeet, Sep 15 '11 at 09:01

Sandeep G B · Answer 1 · 2011-09-15T09:12:50.407

If you have access to the database, I suggest you to modify the SP or write a new SP to return data in the preferred XML format which will be faster. Building XML in-memory is not a good idea for large data set.

FOR XML AUTO - Simple nested tree of XML with each column being represented as a single element
FOR XML RAW - Each row in the result set is transformed into generic element tag
FOR XML EXPLICIT - A predefined XML format is created for the result set
FOR XML PATH - Much of the same functionality as the EXPLICIT mode, but the elements and attributes can be built with XPATH like syntax

Reference

score 0 · Answer 2 · answered Sep 15 '11 at 09:04

I don't see any obvious problem but I'm not surprised that converting 250K row into xml in memory can burn your CPU.

One thing you can do is to //ize the process (one thread per resultSet for example)

I would use the for xml clause of SQL Server instead !

c# SqlDataReader to XElement Efficiently

2 Answers2