.NET XmlReader with 'FOR XML' data

Question

Recently, in company we got some MSSQL database from some old project which we have to integrate in current solution.

Database has about 100-150 stored procedures that use FOR XML AUTO clause, so that queries return complete object-graph as XML instead of rows.

Quickest solution (for us in company) was to create serializable classes (with xsd-tool) based on the xml data returned from database.

This is code we use to instatiate those objects:

 public static T GetObjectFromXml<T>(DbCommand command)
    {
        SqlDatabase db = (SqlDatabase)DB;
        XmlReader xmlReader = null;
        T returnValue;

        xmlReader = db.ExecuteXmlReader(command);
        xmlReader.MoveToContent();

        XmlSerializer serializer = new XmlSerializer(typeof(T));

        returnValue = (T)serializer.Deserialize(xmlReader);

        xmlReader.Close();

        return returnValue;


    }

DB represents Database class from enterprise library.

When sp returns lots of data (for example some big collection of objects with lots of children,grandchildren,grandgrndchldrn...objects in it) execution of this method lasts very long.

The data in application will surrely continue to grow, and I must think of optimizing this.

So, I'm wondering if this is bad practice (using FORXML, XmlReader and Deserialize), or we should rewrite stored procedures and use SqlDataReaders or Linq2Sql, or there is some perf.issue within this snippet (improper use of generics or something else) ?

---

Edit I know that it is bad practice to load big ammount of data at once, and I know that load process should be splitted to smaller chunks, but I'm just wondering if something is wrong with this particular piece of code.

Answer 1

You need to analyze this problem in terms of what's in the XML being returned. Is the XML returning data that doesn't need to be in memory all at once? Then deserializing it all into memory is probably not the best thing. If you only need to process the data a little at a time, then you should perhaps process the XML as XML and keep the XmlReader around to read a little at a time.