主页 / dba / 问题 / 188704
Accepted
user45867
Asked: 2017-10-18 14:00:37 +0800 CST

为什么在 SQL Server 中“select *”比“select top 500 *”快？[复制]

  • 772

我有一个观点complicated_view——有一些连接和 where 子句。现在，

select * from complicated_view (9000 records)

比_ _

select top 500 * from complicated_view

我们说的是 19 秒与 5 分钟以上。

第一个查询返回所有 9000 条记录。仅获得前 500 名的时间怎么会长得离谱？

显然，我将在这里查看执行计划 ---- 但是一旦我弄清楚为什么SQL Server 以次优方式运行“前 500 名”，我实际上如何告诉它以快速的方式运行计划，喜欢吃满桌吗？

当然，我可能不得不完全重写视图 --- 但很奇怪。

基本上，我将此数据表连接到第 3 方软件，该软件使用select top 500 *无法修改的默认查询预检查表。所以除了把这个视图放到一个实际的表中（相当草率）——我也无法绕过他们的“前 500 名”附录。

这是 SQL Server 2012。

编辑：不同意重复标志。另一个问题，顶部比所有都快。这将是预期的行为，返回更少的行。我的情况正好相反。另外，我的理解是 Top 100 是与 Top 100+ 不同的算法。我什至不认为重复的问题有正确的答案。也就是说，TOP X 查询将在很早的时候对潜在的大量表进行排序，而不是在它们被聚合/过滤/等之后。为什么是一个谜，但如何清楚地存在。

sql-server performance

1 个回答

  1. Best Answer

    向查询添加TOP子句会为查询引入行目标。查询优化器将尝试利用它不需要返回所有行来创建更有效的查询计划这一事实。行目标可能会导致某些运算符的成本按比例缩小。由于模型限制或统计对象中的信息不完整，行目标优化可能不利于查询调谐器。下面我有一个针对添加TOP 500会降低性能的简单视图的演示。

    首先只将奇数插入表中。请注意，我在最后收集了完整的统计数据。

    DROP TABLE IF EXISTS dbo.ODD;

CREATE TABLE dbo.ODD (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(10)
);

INSERT INTO dbo.ODD WITH (TABLOCK)
SELECT TOP (100000)
  -1 + 2 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('FLUFF', 2)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE STATISTICS S ON dbo.ODD (ID) WITH FULLSCAN;

    然后只将偶数整数插入到不同的表中。我正在做一些重复值和行大小的事情，以使演示工作。最后我仍然会完整更新统计数据。

    DROP TABLE IF EXISTS dbo.EVEN;

CREATE TABLE dbo.EVEN (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(3500)
);

INSERT INTO dbo.EVEN WITH (TABLOCK)
SELECT TOP (100000) 
  1000 * FLOOR ( ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 500)
, REPLICATE('FLUFF', 700)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2

CREATE STATISTICS S ON dbo.EVEN (ID) WITH FULLSCAN;

    这是视图定义：

    CREATE OR ALTER VIEW dbo.TRICKY_VIEW AS
SELECT o.ID
FROM dbo.ODD o
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.EVEN e WHERE o.ID = e.ID
);

    考虑以下查询：

    SELECT TOP 500 *
FROM dbo.TRICKY_VIEW
OPTION (MAXDOP 1);

    查询计划如下所示：

    糟糕的计划

    成本计算限制EVEN导致对嵌套循环连接内侧的表进行全扫描具有较低的相对成本。根据我构建数据的方式，我们知道优化器需要从EVEN表中扫描 500 * 100000 = 5000 万行，以便将前 500 行返回给客户端。这确实发生了，查询在我的机器上执行大约需要 16 秒：

    糟糕的实际计划

    从查询中删除TOP子句给出了一个不同且更有效的计划：

    好计划

    这个查询在我的机器上执行不到半秒。EVEN从表中仅读取 100000 行。

    OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))对于 SQL Server 2016 及更高版本，您可以通过添加到查询来解决此问题，而无需更改视图的定义。该提示在查询级别禁用行目标优化。对于 SQL Server 2012，您可以通过在查询级别使用跟踪标志 4138 OPTION (QUERYTRACEON 4138)，但这需要 SA。

    在没有查看查询计划的情况下，我无法特别说明您的查询，但希望此示例说明了一般观点。

    • 29

相关问题