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

Adding inner query is not changing the execution plan

sql-execution-plan sql-server-2005 performance sql

Navaneeth K N · 技术社区 · 16 年前

考虑以下查询。

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 回复 | 直到 15 年前

AdaTheDev 16 年前

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.

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

1 年前

John D · 需要为NULL或NOT NULL的WHERE子句

1 年前

ojek · 如何对SQL结果进行分组和编号?

1 年前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

1 年前

Sax · 规范化Google表格(第一步)

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

1 年前