SQL Server 性能 - 子选择或内部联接?

SQL Server performance - Subselect or Inner Join?(SQL Server 性能 - 子选择或内部联接?)

问题描述

我一直在思考这两个语句中哪一个可能具有更高的性能(以及为什么):

I've been pondering the question which of those 2 Statements might have a higher performance (and why):

select * from formelement 
where formid = (select id from form where name = 'Test')

或

select * 
from formelement fe 
inner join form f on fe.formid = f.id 
where f.name = 'Test'

一个表单包含多个表单元素，一个表单元素始终是一个表单的一部分.

One form contains several form elements, one form element is always part of one form.

谢谢，

丹尼斯

推荐答案

性能取决于 SQL Server 引擎选择的查询计划.查询计划取决于很多因素，包括(但不限于)SQL、确切的表结构、表的统计信息、可用索引等.

The performance depends on the query plan choosen by the SQL Server Engine. The query plan depends on a lot of factors, including (but not limited to) the SQL, the exact table structure, the statistics of the tables, available indexes, etc.

由于您的两个查询非常简单，我猜它们会产生相同(或非常相似)的执行计划，从而产生相当的性能.

Since your two queries are quite simple, my guess would be that they result in the same (or a very similar) execution plan, thus yielding comparable performance.

(对于大型、复杂的查询，SQL 的确切措辞可以有所作为，SQL Tuning by Dan Tow 提供了很多很好的建议.)

(For large, complicated queries, the exact wording of the SQL can make a difference, the book SQL Tuning by Dan Tow gives a lot of great advice on that.)

这篇关于SQL Server 性能 - 子选择或内部联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！