We do a FOR XML query using SQLXMLReader.ExecuteXMLReader() method. The
results is obtained in a XMLReader. We then load XMlDocument using XmlReader
as we have to process the XML.

When we try to fire a query which returns huge data, the query executed by
database is 6 seconds but to load the XML it takes 30 minutes. It could be
30-50 MB xml.

Is there any better way to load the XML.

Hiya,

When you say it took 6 seconds for the query to complete, are you sure it's
actually completed? Even though the reader starts returning data, i'm fairly
sure this does not mean all the data for the query has been full prepared.
If i'm not talking garbage, it could mean that your query is actually taking
30 minutes to fully complete, in which case, optimizing your query may be
better then changing the way you load your xml

Andrew

"Debasish Pramanik" <Debasish Pramanik (AT) discussions (DOT) microsoft.com> wrote in
message news:994F1554-46DF-47C5-AE32-839334B002F2 (AT) microsoft (DOT) com...

Hi Andrew:

Thanks for quick response.

This is the way I validated.

Step 1: I executed the query with DOM loading. This took almost 30 minutes.
I did this twice and result were same.

Step 2: I then executed only the query without loading the XML. It just took
6 seconds on an average for 10 iteration.

This was the basis of my findings.

Let me just dump the code

String Query = "Select .... FOR XML EXPLICT";

XmlReader reader = sqlXMLReader.ExecuteReader(Query); <== this takes 6 seconds
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xmlReader); <==== This takes 30 minutes



"Andrew Brook" wrote:

Hi Debasish,

I think this still matches my earlier comment, I think even though your call
to ExecuteReader returns almost immediately, the data is not all available.
I think it's like when you execute a query in the query analyser, some rows
appear quite quickly (depending on the query) but it can take some time
before all the rows are returned.

As a further test, take the DOM out of the equation, try executing the
reader and then interating of the rows that are returned. If i'm right then
it should still take in the region of 30 mins to complete.

Code would be something like:

XmlReader reader = sqlXMLReader.ExecuteReader(Query);
int count = 0;
while (reader.read())
{
count ++;
}

thanks,
Andrew

"Debasish Pramanik" <Debasish Pramanik (AT) discussions (DOT) microsoft.com> wrote in
message news:207B5E5C-0D2B-4DBC-B895-97EB25E43758 (AT) microsoft (DOT) com...