哪个 SQL 查询更快，为什么? [英] Which SQL query is faster and why?

问题描述

最近，我被要求编写一个查询，以从包含最大数量的此类实体的组中选择实体的属性.因此，我通过多种方式在 Northwind(MSFT 分布式样本)数据库上完成了这项工作.

Recently, I've been asked to write a query to select properties of entities from a group that contains the maximum number of such entities. So, I did it on Northwind (MSFT distributed sample) database in a couple of ways.

一:

SELECT cat.CategoryName, prod.ProductName 
  FROM Categories cat 
  JOIN Products prod ON cat.CategoryID = prod.CategoryID
  JOIN (SELECT TOP 1 p.CategoryID, COUNT(p.ProductId) as products
          FROM Categories c 
          JOIN Products p on c.CategoryID = p.CategoryID
      GROUP BY p.CategoryID
      ORDER BY products desc) c ON c.CategoryID = cat.CategoryID

两个:

SELECT cat.CategoryName, prod.ProductName
  FROM Categories cat
  JOIN Products prod ON cat.CategoryID = prod.CategoryID
  JOIN (SELECT CategoryID, COUNT(ProductID) m_count
          FROM Products 
      GROUP BY CategoryID
        HAVING COUNT(ProductID) = (SELECT MAX(sub.cnt) 
                                     FROM (SELECT CategoryId, COUNT(ProductID) cnt 
                                             FROM Products 
                                         GROUP BY CategoryId) sub)) m ON m.CategoryID = cat.CategoryID

问题是:哪个更快?在执行计划中，没有什么特别突出的.经过的时间略有不同，但大致相同.当然，数据库很小.

The question is: which is faster any why? In execution plans nothing stands out, in particular. The elapsed time varies slightly, but is the same, approximately. The database is, of course, tiny.

推荐答案

很小的数据库让人很难确定哪个更好，但 SQL Server Management Studio 具有将语句效率相互比较的功能.

A tiny database makes it difficult to determine which is better, but SQL Server Management Studio has functionality to compare the efficiency of statements to one another.

1. 开放管理工作室
2. 点击新建查询"按钮
3. 单击以启用包括实际查询计划"
4. 将所有查询发布到活动查询窗口
5. 点击执行"按钮按钮
6. 点击执行计划"选项卡(在结果左侧)出现时

查询成本由运行的查询数量平均.因此，如果比较作为示例提供的两个查询，如果两者的成本均为 50%，则它们是等效的(因为 100/2 = 50 等).当有差异时，除了查看执行路径的图形布局外，还可以将鼠标悬停在 SELECT 上查看子树成本.

The query cost is averaged by the number of queries run. So if comparing the two queries provided as examples, if both have a cost of 50% then they are equivalent (because 100 / 2 = 50, etc). When there's a difference, you can mouseover the SELECT to review the subtree cost, besides looking at the graphical layout of the Execution path.

这取决于数据库——被连接的数据类型(它们是否尽可能窄?窄"意味着使用更少的字节来存储)、索引以及查询中正在执行的操作.使用不同的语法可以让一切变得不同.

That depends on the database -- the data types being joined (are they as narrow as they could be? "narrow" means taking less bytes to store), indexes, and what is being performed in the query. Using different syntax can make all the difference.

这篇关于哪个 SQL 查询更快，为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！