MS SQL XML - Query performance disparity (ASP)

Author

Query performance disparity (ASP)

DrewM

2005-07-14, 8:24 pm

I have a largish query which I'm retrieving as XML with the "FOR XML
RAW" clause. Having implemented the SQLXML 3 registry keys to turn on
caching, the query returns in Query Analyser in about 5 seconds.

The client app is an ASP web application running locally to the database
server. I'm using an ADO Stream and the SQLXMLOLDB provider to perform
the query.

The trouble I'm having is that the same query run from the ASP page
takes well in excess of 60 seconds to return .. sometimes longer. It's
difficult to time as often the ASP page will time-out before the query
ever returns.

A massive disparity between the performance from ASP and Query Analyser.
Any suggestions where I should look?

Many thanks.

drew

Michael Rys [MSFT]

2005-07-16, 3:23 am

This is hard to say. Can you please provide a repro sample (code and data)?

Thanks
Michael

"DrewM" <callmedrew@gmail.com> wrote in message
news:- NOdnSOiQflNdkvfRVnyj
Q@pipex.net...
>I have a largish query which I'm retrieving as XML with the "FOR XML RAW"
>clause. Having implemented the SQLXML 3 registry keys to turn on caching,
>the query returns in Query Analyser in about 5 seconds.
>
> The client app is an ASP web application running locally to the database
> server. I'm using an ADO Stream and the SQLXMLOLDB provider to perform the
> query.
>
> The trouble I'm having is that the same query run from the ASP page takes
> well in excess of 60 seconds to return .. sometimes longer. It's difficult
> to time as often the ASP page will time-out before the query ever returns.
>
> A massive disparity between the performance from ASP and Query Analyser.
> Any suggestions where I should look?
>
> Many thanks.
>
> drew

Sponsored Links