SQL Server Performance Forum – Threads Archive

Query performance disparity

Firstly, apologies if this turns out not to be a SQL Server problem and would be best posted elsewhere. I’m completely baffled and need a starting point! 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

run a trace and check sql profiler what is going wrong.
i feel the query will take 3 seconds from asp page too there can be some network issues.

Referhttp://www.sql-server-performance.com/xml.asp also for information. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks satya – I’ve already been through that page (it’s well indexed by Google), and there’s no immediate solution I can see. ranjitjain – I don’t think network issues are at play here. IIS and SQL Server are on the same box, and I’m accessing it across a non-busy LAN.

Have you tried to run app on other server then database one?

BTW can you confirm the memory settings on SQL Server? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>