為什麼估計的行隨著每次連接呈指數增長？

概括

我們有一個物件導向的數據庫模式，我們用 Hibernate 查詢它：

• 1 個父表 (NAL_Actions)
• 7 個子表 (NAL_*)

如果我想查詢這些“動作”對像中的幾個，我們必須將所有 1+7 個表連接在一起（因為我們不知道我們需要什麼類型的子對象），這不是很好，而是因為查詢執行SQL Server 生成的計劃導致查詢非常慢（索引掃描 250 萬行）。

細節

父表也有一個外鍵（actionGroups），根據全掃描統計結果平均有 1.15 個“action”-rows。

這個查詢計劃-edit, now also on: 粘貼計劃過度估計了每個後續連接表的行數，我和統計數據知道這些表永遠不會超過兩行，因為所有表都在它們的（公共）不同主鍵上連接，即還有聚集索引，這意味著估計的行數不能增加，從這個子表之一的定義可以看出：

USE [NAL_PRD]
GO

/****** Object:  Table [dbo].[NAL_Unblocks]    Script Date: 6/24/2020 6:04:40 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NAL_Unblocks](
   [actionId] [bigint] NOT NULL,
   [unblockingAmount] [decimal](5, 2) NOT NULL,
   [productSequenceNumber] [int] NULL,
CONSTRAINT [PK_NAL_UNBLOCKS] PRIMARY KEY CLUSTERED 
(
   [actionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NAL_Unblocks]  WITH CHECK ADD  CONSTRAINT [fk_unblocks_actions] FOREIGN KEY([actionId])
REFERENCES [dbo].[NAL_Actions] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[NAL_Unblocks] CHECK CONSTRAINT [fk_unblocks_actions]
GO

由於 SQL Server 錯誤地假設每個連接表的行數呈指數增長，因此它會更改最後 3 個表的連接策略：

• 來自：“聚集索引搜尋 + 嵌套循環”
• to：“聚集索引掃描+合併連接”

這意味著我們需要掃描 2,500,000 行的表的聚集索引，而不是 1 或 2 次索引查找

到目前為止我嘗試過的

1. 提示有效，例如：WITH(FORCESEEK)對於每個連接，或：OPTION (LOOP JOIN)對於整個查詢，（但我無法更改應用程序）
2. 這導致我建議將計劃指南作為下一個選項（對於 SQL Server 2016 - 13.0.5102.14），但儘管它適用於 sp_executesql，但計劃指南似乎不會在應用程序中立即生效，儘管我從查詢中獲取了 SQL儲存和更新統計資訊（但可能更新統計資訊是一個錯誤的假設，所以我可能需要重新啟動應用程序和/或放棄計劃 - 但是如果沒有 DBCC FREEPROCCACHE，因為我僅限於 db_owner 權限，所以沒有 VIEW/ALTER SERVER STATE 權限? 也許我應該切換應用程序/JDBC 驅動程序以使用帶有prepareSQL=2 的sp_executesql 而不是預設的(sp_prepare + sp_execute) ?。

我還沒有嘗試過的：

• 升級到 SQL Server 2017 以嘗試自適應查詢處理，應該解決這個問題嗎？

為了避免在我們使用此類查詢的所有不同地方更改應用程序，並且由於緊迫性高，客戶同意啟用舊的基數估計器，之後所有問題都立即得到解決：

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY CARDINALITY ESTIMATION = ON;

我要感謝大家為這個問題貢獻了寶貴的時間和知識。

當您使用休眠時，這會有點麻煩。您可以通過告訴優化器如何進行連接來“指導”優化器。例如select * from a inner hash join b on a.x = b.x會告訴優化器做一個雜湊連接。它也可以是合併連接或循環提示。

這可能會導致手動編寫 sql 查詢。使用提示也應該是最後嘗試的事情之一。您的表格可能會隨著時間的推移而變化，並且提示可能不適用於後期的性能/對性能有好處（主要是從開發切換到生產時）。

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