為什麼在 SQL Server 中“select *”比“select top 500 *”快？

我有一個觀點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 查詢將在很早的時候對潛在的大量表進行排序，而不是在它們被聚合/過濾/等之後。為什麼是一個謎，但如何清楚地存在。

向查詢添加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。

在沒有查看查詢計劃的情況下，我無法特別說明您的查詢，但希望此範例說明了一般觀點。

引用自：https://dba.stackexchange.com/questions/188704