分页结果集中的选择子查询或左外连接哪个更快 [英] Which is faster a select sub-query or a left outer join in a paginated result set

问题描述

使用 MSSQL 查询时，从计算表中获取单个列并将其连接到结果集的最有效方法是什么.

When querying with MSSQL what is the most efficient way to grab a single column from a computed table and connect it to a result set.

表:mytable = mytable.col1 上的 20k 行索引，othertable = 30k 行，othertable.col1 上没有索引

Tables: mytable = 20k rows index on mytable.col1, othertable = 30k rows, no index on othertable.col1

内联查询 - 在 select 语句中运行查询

Inline Query - Runs an query in the select statement

SELECT * FROM (
    SELECT col1, col2, col3, 
    col4 = (SELECT min(col5) FROM othertable o WHERE m.col1 = o.col1)
    row = ROW_NUMBER() OVER(ORDER BY somerow) 
    FROM mytable m
) as paged
WHERE row BETWEEN 1 AND 25

Join Query - 将我们的表连接到计算表上

Join Query - Joins our table onto the computed table

SELECT * FROM (
    SELECT col1, col2, col3, o2.col5again
    row = ROW_NUMBER() OVER(ORDER BY somerow) 
    FROM mytable m
    JOIN (SELECT col1, min(col5) as col5again FROM othertable o GROUP BY col1) as o2 ON o2.col1 = m.col1
) as paged
WHERE row BETWEEN 1 AND 25

我的直觉是 JOIN 更快.然而，在测试时，内联查询将在平均 7 秒内完成，而在 MSSQL Studio 中执行时，其他查询将需要 30 秒以上.

My gut instinct was the JOIN was faster. Yet, upon testing the inline query would finish in an average of 7 seconds while the other query would take >30 seconds when executed in MSSQL studio.

1. 使用内联选择查询真的是注入单列的最佳方式吗?
2. 优化的查询是否等待运行内联 SELECT() 语句直到结果被分页，这是否可以解释运行时间的不同?

1. Is using an inline select query really the best way to inject the single column?
2. Does the query optimized wait to run the inline SELECT() statement until after the results have been paged, would that explain the different in running time?

仅供参考:在我的具体示例中，我们向 othertable.col1 添加了一个索引，并将查询时间减少到 0 秒，但这个问题更侧重于 JOIN 与 SELECT() 是否更好.

FYI: In my specific example we added an index to othertable.col1 and it reduced the query time to 0s, but this question focuses more on whether the JOIN versus SELECT() is better.

推荐答案

性能调优是一门艺术，它涉及理解为什么要以这种方式创建计划、计划中的每个部分都做了什么，以及如何影响一个更有效的要选择的路径.

Performance tuning is an art that involves understanding why plans are created the way they are, what each piece in the plan does, and how you can influence a more efficient path to be chosen.

缺少覆盖索引会导致扫描(过度读取).太多的索引会减慢你的 DUI(删除、更新和插入).过时或缺失的统计信息将导致使用错误的连接算法和/或估计行数不准确(这可能导致分页).

Missing covering indexes will cause scans (excessive reads). Too many indexes will slow down your DUI's (Deletes, Updates, and Inserts). Outdated or missing statistics will cause the wrong join algorithm to be used and/or an inaccurate estimated row count (which could lead to paging).

在同一窗口中运行两个查询并包含实际的执行计划.这将拆分计划并告诉您哪个更贵.它会给你缺少索引提示.随着您精通阅读计划，您将了解如何提高查询性能.

Run both queries in the same window and include the actual execution plan. This will split the plans up and tell you which one is more expensive. It will give you missing index hints. As you get better at reading plans, you'll learn how to improve your query performance.

这篇关于分页结果集中的选择子查询或左外连接哪个更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！