代码之家  ›  专栏  ›  技术社区  ›  Navaneeth K N

Adding inner query is not changing the execution plan

  •  2
  • Navaneeth K N  · 技术社区  · 15 年前

    考虑以下查询。

    select * from contact where firstname like '%some%'
    
    select * from 
        (select * from contact) as t1 
    where firstname like '%some%'
    

    两个查询的执行计划是相同的,同时执行。但是,我希望第二个查询将有不同的计划,并且执行得更慢,因为它必须从contact中选择所有数据并应用filter。看来我错了。

    I am wondering how this is happening?

    Database Server : SQL server 2005

    1 回复  |  直到 14 年前
        1
  •  6
  •   AdaTheDev    15 年前

    The "query optimiser" is what's happening. When you run a query, SQL Server uses a cost-based optimiser to identify what is likely to be the best way to fulfil that request (i.e. it's execution plan). Think about it as a route map from Place A to Place B. There may be many different ways to get from A to B, some will be quicker than others. SQL Server will workout different routes to achieve the end goal of returning the data that satisfies the query and go with one that has an acceptable cost. Note, it doesn't necessarily analyse EVERY possible way, as that would be unnecessarily expensive.

    In your case, the optimiser has worked out that those 2 queries can be collapsed down to the same thing, hence you get the same plan.