Passing Xml output from Stored Procedure to AJAX client (web browser) with least overhead (memory, processing time) due to the Asp.Net middle tier?

I am using JQuery to perform async calls to a WebService written in C#. The WebService invokes a Stored Procedure (located in a SQL Server Database). The stored procedure returns results as XML (I use 'for xml' in the procedure to retrieve the result as XML).

The question is: How can I place the XML returned by the stored procedure (that is streamed by SQL Server) on to the HTTP Response stream of the WebService with the least amount of memory and processing time usage.

At the moment, my WebService returns XmlDocument object.I would like to know if there is a way to reduce the overhead of creation of XmlDocument or DataSet Objects, which consume both memory and processor time.

Ideal solution would be: Connect the result stream pipe from SQL Server to the HTTP Response stream pipe. This way, whatever is coming from SQL Server will get to the client without the content being touched. No new objects are created (like DataSet, XmlDocument) and XmlParsing is avoided while creating XmlDocuments. Thus keeping the memory and processing footprint to the least. Also, whether the XML returned by the procedure is small or very large, since streams are used, memory usage will not grow (which is the case is XmlDocuments are created).

My reduced c# webservice method:

public XmlDocument GetXmlDataFromDB()
{
    string connStr = System.Convert.ToString(
            System.Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString("DbConnectionString"),
            System.Globalization.CultureInfo.CurrentCulture);

    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand sqlCmd = new SqlCommand("stp_GetXmlData", conn);
    sqlCmd.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
  开发者_StackOverflow社区  DataSet ds = new DataSet();
    conn.Open();

    XmlReader xmlReader = sqlCmd.ExecuteXmlReader();

    while (xmlReader.Read())
    {
        ds.ReadXml(xmlReader);
    }

    conn.Close();

    XmlDocument xmlDoc = new XmlDocument();
    xmlDoc.InnerXml = ds.GetXml();

    return xmlDoc;
}

Any suggestions to improve the performance of the above code?

In your code, you don't need DataSet, SqlDataAdapter and InnerXml

var doc = new XmlDocument();
...

var reader = cmd.ExecuteXmlReader();
if (reader.Read())
 doc.Load(reader);

Alternatively you can use reader.ReadOuterXml() to get xml as string,without constructing a document.

Also you can think of using WCF Services for ASP.NET AJAX

Obligatory warning:

"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil" - Donald Knuth

Having said that, you could make SQL Server return a string instead of an xml type, like:

; with Query(XmlColumn) as
        (
        SELECT  *
        FROM    YourTable
        for xml auto
        )
select  cast(XmlColumn as varchar(max)) as StringColumn
from    Query

The with construct gives a name to the for xml result. You can stream the resulting varchar(max) column back to the client with Response.Write. This would avoid any parsing or object construction in the ASP.NET middle tier.