SQL Server Performance

Query performance disparity

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by drewm, Jul 14, 2005.

  1. drewm New Member

    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
  2. ranjitjain New Member

    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.
  3. satya Moderator

  4. drewm New Member

    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.
  5. mmarovic Active Member

    Have you tried to run app on other server then database one?
  6. satya Moderator

    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.

Share This Page