添加内部查询不会改变执行计划 [英] Adding inner query is not changing the execution plan

问题描述

考虑以下查询.

select * from contact where firstname like '%some%'

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

两个查询的执行计划相同并且同时执行.但是，我期望第二个查询将有不同的计划并且执行得更慢，因为它必须从联系人中选择所有数据并应用过滤器.看来我错了.

The execution plans for both queries are same and executes at same time. But, I was expecting the second query will have a different plan and execute more slowly as it has to select all data from contact and apply filter. It looks like I was wrong.

我想知道这是怎么发生的?

I am wondering how this is happening?

数据库服务器:SQL server 2005

Database Server : SQL server 2005

推荐答案

查询优化器"就是正在发生的事情.当您运行查询时，SQL Server 使用基于成本的优化器来确定什么可能是满足该请求的最佳方式(即它的执行计划).把它想象成一张从 A 地到 B 地的路线图.从 A 到 B 可能有很多不同的方式，有些会比其他的更快.SQL Server 将尝试不同的路线来实现最终目标，即返回满足查询的数据并选择具有可接受成本的数据.请注意，它不一定会分析所有可能的方式，因为那样会不必要地昂贵.

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.

在您的情况下，优化器已经计算出这 2 个查询可以折叠为相同的内容，因此您会得到相同的计划.

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.

这篇关于添加内部查询不会改变执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！