Read UTF8(XML) Data from SQL Server 2005 the most efficient way

Question

I want to read lots of data(single column nvarchar(max)) from SQL Server 2005 and deserialize it to an object. We are currently using the following, but this is not fast enough is there a better/efficient way to do it?

using(MemoryStream stream = Encoding.UTF8.GetBytes((string)cmd.ExecuteScalar()))
{
  XmlTextReader xmlReader;
  DataContractSerializer deserializer;

  deserializer = new DataContractSerializer(typeToDeserialize);
  xmlReader = new XmlTextReader(stream);
  return deserializer.ReadObject(xmlReader);
} 

I've als tried to do it with an SqlDataReader and GetBytes but then I get exceptions.

Thanx in advance.

Answer 1

Do you have the option of switching to use the XML datatype? It stores the data in a binary format, and exposes XML results as an XmlReader instance. You're parsing the data as you read it from the database, but if you used an XML column, it would already be parsed.

In the meantime, try something like this:

string s;
using (SqlConnection conn = new SqlConnection(""))
{
    using (SqlCommand cmd = new SqlCommand("", conn))
    {
        s = (string) cmd.ExecuteScalar();
    }
}
using (StringReader sr = new StringReader(s))
{
    using (XmlReader reader = XmlReader.Create(sr))
    {
        DataContractSerializer deserializer = 
           new DataContractSerializer(typeToDeserialize);
        return deserializer.ReadObject(reader);
    }
}

1. Don't use XmlTextReader anymore.
2. Why convert the string to bytes and back to string?

Answer 2

I haven't tested this myself, but SqlDataReader with a call to GetSqlBytes seems like a better choice since it exposes a Stream property which you should be able to pass directly to XmlTextReader?

There's some information on MSDN about GetSqlBytes here.