Post reply

Query optimization for correlated query in sql server 2005

  • January 11, 2011 at 11:40 pm

    #233126

    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

  • January 11, 2011 at 11:41 pm

    #1272194

    Please post query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    There's nothing in general wrong with correlated subqueries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 12, 2011 at 1:22 pm

    #1272607

    Like GilaMonster said, there is nothing inherently wrong with correlated queries other than they're often incorrectly used.

    I find they work extremely well to check for existence of relations on tables with a heavy ratio of one-to-many.

    Example. I had a DB with a design of courses, classes, classstaff, and class roster(only 4 tables). A class is attached to a course and Staff/Roster are attached to a class.

    The query returned a list of courses that had at least 1 class that had at least one student and teacher assigned. About 10,000 courses with about 15 classes per course and 20 students per class and typically one teacher per class.

    The original query did nothing other than inner join the tables together and return a distinct list of courses and it took about 10 minutes without indexes. I indexed the tables and got it down to about 10 seconds.

    I changed the code from inner joins to selecting from the course table where exists(classes where exists(roster) and exists(staff)). Got it down to ~200ms. Ten minutes down to 200ms, I was content.

    Would be nice to see the code and the table structure.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply