Andrew Brook
12/10/2007 11:58:00 AM
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@discussions.microsoft.com> wrote in
message news:207B5E5C-0D2B-4DBC-B895-97EB25E43758@microsoft.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:
>
>> 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@discussions.microsoft.com> wrote
>> in
>> message news:994F1554-46DF-47C5-AE32-839334B002F2@microsoft.com...
>> > 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.
>>
>>
>>