SQL Server Performance

Query optimization for correlated query in sql server 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by sasivashok, Jan 11, 2011.

  1. sasivashok New Member

    Hi friends,
    I have a clarification for query optimization technique in sql server 2005.
    I have a select query in sp. In this query, i put correlated query depends upon the main select query table.
    Actually one main query in where condition i am checking condition of another select query. If you run this
    correlated query, it showed error. So this correlated query depends upon the main query. Here i come with one
    clarification that it is taking time when run this query. So i need a general aware of how to reduce time when using
    correlated sub queries in sql server2005. Useful suggestion welcomes and hope it helpful to me.
    Thanks
    Ashok
    sasivashok, Jan 11, 2011
    #2

  2. FrankKalis Moderator

    Can you please post the query that caused the error? One common error in this regard is that the subquery returns more than 1 rows. Maybe that is the case with you as well?
    FrankKalis, Jan 11, 2011
    #3

  3. sasivashok New Member

    Hi,
    It doesn't give the error. I mentioned that run correlated sub query without main query it showed exception.
    It is working fine. I am asking that what is recommended way to optimize instead of using correlated
    subquery. How can reduce the time?
    Thanks
    Ashok
    sasivashok, Jan 11, 2011
    #4

  4. satya Moderator

    What is the service pack level oN SQL Server?
    What are the indexes involved in these queries?
    Did you check execution plan for this main query?
    Due to the fact that the sub-query in a correlated sub-query can be executed for every row returned in the outer query, performance can be degraded. With a sub-query, performance is totally dependent upon the query and the data involved.
    Instead of using correlated queries why not take use of JOINS in this case, see this blog http://jahaines.blogspot.com/2009/06/correlated-sub-queries-for-good-or-evil.html post for information.
    satya, Jan 12, 2011
    #5

  5. FrankKalis Moderator

    Ah, I guess I just misread your question then. As Satya suggested have a look at the execution plan. Many times you'll find that the Query Optimizer rewrites such constructs to a JOIN operation internally. If not, make sure the indexes in place are useful for the query
    FrankKalis, Jan 12, 2011
    #6

  6. Luis Martin Moderator

    Also use DTA to check indexes used and suggested.
    Luis Martin, Jan 12, 2011
    #7

Share This Page