SQL Server Performance

Comparison of 3 different Queries with same result

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by lucutus, Nov 29, 2007.

  1. lucutus New Member

    Hello All!
    Im trying to figure out which variant of a query (subquery with EXISTS, subquery with IN, one overall join with group and having) is the best.
    I executed all three queries at once and display the execution plan.
    The first had query costs (relative to the batch) of 6%, the second 66%, the third 28%.
    Now I would like to know: Does this definitly mean the first variant (with EXISTS and subquery) is the best?
    Note that the database was fully populated and is actually the same as the one in production.
    I could post the queries, but the real question is: Does this method work to compare queries against each other that end up in the same result set?
    Thx in advance, greetings

  2. FrankKalis Moderator

    Such a result is pretty obvious. It really seems that the first query has the most effective execution plan in your current environment. It does not necessarily mean, that queries 2 & 3 are poor per se. They might probably be improved by a change to the database by adding or modifying indexes.
  3. Madhivanan Moderator

  4. Adriaan New Member

    The funny thing is that most of these execution plans involving EXISTS that I've seen tend to overestimate the cost for the EXISTS version.
    Now I'm a SQL 2000 guy, so perhaps they corrected that anomaly in SQL 2005? That would be great.
  5. lucutus New Member

    It really seems that (in SQL 2005) the IN (subquery) is overestimated. The EXISTS version runs definitly fine and the Plan shows 6 Index seeks. The IN Version shows 4 Index Seeks.
    I dont know much about query plans, but Index Seek is definitly what I want ;)
    I will go on with other queries and compare them with this "method".
    How do you guys compare different approaches/solutions to the same problem? What else could I test. Changing and adding Indexes is not an option for me right now...
    greetz, Lucutus
  6. FrankKalis Moderator

    Actually I tend to write a query in the way that is most natural to me. That means, if I want to test for existance, I tend to use EXISTS(). Only if that doesn't yield good performance, I seek for other alternatives. In that process I compare the execution plans and the query costs, just as you did. Since you cannot add or change indices, this is the most you can do right now.
    And, yes, Adriaan, in SQL Server 2005 MS optimized EXISTS subquery. Typically they now perform better than LEFT JOIN queries.
  7. Adriaan New Member

    Frank - I didn't know the performance of EXISTS subqueries has improved with SQL 2005. My question was purely about EXISTS and IN subqueries being weighed as equal in execution plans in SQL 2000.

Share This Page