Sql-Server

包含所有列的空結果需要更長的時間

  • January 12, 2018

我每 1 秒執行一次查詢，大多數時候它不返回任何結果（實體框架生成的 SQL）：

SELECT TOP (5) 
  [Extent1].[ID] AS [ID],
  ***30 more columns***

  FROM ParentTable AS [Extent1]
  WHERE ([Extent1].[ImageTaken] = 1) AND ([Extent1].[ImageProjected] <> 1) AND ( EXISTS (SELECT 
     1 AS [C1]
     FROM ChildTable AS [Extent2]
     WHERE [Extent1].[ID] = [Extent2].[Parent_ID]
  ))

上述查詢大約需要 400 毫秒。但是，如果我從結果中排除除 ID 之外的所有列，則大約需要 100 毫秒。

如果沒有結果，為什麼執行時間會如此不同？我查看了執行計劃，它們看起來是一樣的（在今天之前我從未看過執行計劃，所以請謹慎對待）。

我想包括所有列，但顯然只有在有結果時才需要它們。

實際執行計劃

所有列

僅 ID 列

編輯 更多細節：

  • 我正在使用 SQL Server Express 2017
  • SQL Server 在我的本地機器上
  • 父表有大約 100 萬行
  • 子表有 ~40k 行

父表定義


   USE [DB]
   GO

   /****** Object:  Table [dbo].[Inspection_CapturedImageQueueItem]    Script Date: 1/8/2018 6:23:45 PM ******/
   SET ANSI_NULLS ON
   GO

   SET QUOTED_IDENTIFIER ON
   GO

   CREATE TABLE [dbo].[Inspection_CapturedImageQueueItem](
       [ID] [INT] IDENTITY(1,1) NOT NULL,
       [X] [FLOAT] NOT NULL,
       [Y] [FLOAT] NOT NULL,
       [Z] [FLOAT] NOT NULL,
       [rX] [FLOAT] NOT NULL,
       [rY] [FLOAT] NOT NULL,
       [rZ] [FLOAT] NOT NULL,
       [Priority] [INT] NOT NULL,
       [TimeTakenUTC] [datetime] NOT NULL,
       [ImageTaken] [bit] NOT NULL,
       [PartProgramInstance_Id] [INT] NULL,
       [PolarizerAngle1] [FLOAT] NOT NULL,
       [PolarizerAngle2] [FLOAT] NOT NULL,
       [ImageProjected] [bit] NOT NULL,
       [LaserTransform_X] [FLOAT] NOT NULL,
       [LaserTransform_Y] [FLOAT] NOT NULL,
       [LaserTransform_Z] [FLOAT] NOT NULL,
       [LaserTransform_rX] [FLOAT] NOT NULL,
       [LaserTransform_rY] [FLOAT] NOT NULL,
       [LaserTransform_rZ] [FLOAT] NOT NULL,
       [LaserPosition_X] [FLOAT] NOT NULL,
       [LaserPosition_Y] [FLOAT] NOT NULL,
       [LaserPosition_Z] [FLOAT] NOT NULL,
       [LaserPosition_rX] [FLOAT] NOT NULL,
       [LaserPosition_rY] [FLOAT] NOT NULL,
       [LaserPosition_rZ] [FLOAT] NOT NULL,
       [ProjectionError] [INT] NOT NULL,
       [LocalTransform_X] [FLOAT] NOT NULL,
       [LocalTransform_Y] [FLOAT] NOT NULL,
       [LocalTransform_Z] [FLOAT] NOT NULL,
       [LocalTransform_rX] [FLOAT] NOT NULL,
       [LocalTransform_rY] [FLOAT] NOT NULL,
       [LocalTransform_rZ] [FLOAT] NOT NULL,
       [IsHighAngleOfIncidence] [bit] NOT NULL,
    CONSTRAINT [PK_dbo.Inspection_CapturedImageQueueItem] PRIMARY KEY CLUSTERED
   (
       [ID] 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].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [PolarizerAngle1]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [PolarizerAngle2]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [ImageProjected]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserTransform_X]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserTransform_Y]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserTransform_Z]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserTransform_rX]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserTransform_rY]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserTransform_rZ]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserPosition_X]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserPosition_Y]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserPosition_Z]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserPosition_rX]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserPosition_rY]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LaserPosition_rZ]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [ProjectionError]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LocalTransform_X]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LocalTransform_Y]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LocalTransform_Z]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LocalTransform_rX]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LocalTransform_rY]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [LocalTransform_rZ]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] ADD  DEFAULT ((0)) FOR [IsHighAngleOfIncidence]
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Inspection_CapturedImageQueueItem_dbo.Inspection_PartProgramInstance_PartProgramInstance_Id] FOREIGN KEY([PartProgramInstance_Id])
   REFERENCES [dbo].[Inspection_PartProgramInstance] ([Id])
   GO

   ALTER TABLE [dbo].[Inspection_CapturedImageQueueItem] CHECK CONSTRAINT [FK_dbo.Inspection_CapturedImageQueueItem_dbo.Inspection_PartProgramInstance_PartProgramInstance_Id]
   GO

子表定義


USE [DB]
GO

/****** Object:  Table [dbo].[Inspection_CapturedImageItem]    Script Date: 1/11/2018 9:01:34 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Inspection_CapturedImageItem](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [PolarizerAngle] [float] NOT NULL,
   [ImageRequest_ID] [int] NULL,
   [TimeTakenUTC] [datetime] NOT NULL,
   [ProjectorNumber] [int] NOT NULL,
   [AngleOfIncidence] [float] NOT NULL,
   [MirrorRx] [float] NOT NULL,
   [MirrorRy] [float] NOT NULL,
CONSTRAINT [PK_dbo.Inspection_CapturedImageItem] PRIMARY KEY CLUSTERED 
(
   [ID] 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].[Inspection_CapturedImageItem] ADD  DEFAULT ('1900-01-01T00:00:00.000') FOR [TimeTakenUTC]
GO

ALTER TABLE [dbo].[Inspection_CapturedImageItem] ADD  DEFAULT ((0)) FOR [ProjectorNumber]
GO

ALTER TABLE [dbo].[Inspection_CapturedImageItem] ADD  DEFAULT ((0)) FOR [AngleOfIncidence]
GO

ALTER TABLE [dbo].[Inspection_CapturedImageItem] ADD  DEFAULT ((0)) FOR [MirrorRx]
GO

ALTER TABLE [dbo].[Inspection_CapturedImageItem] ADD  DEFAULT ((0)) FOR [MirrorRy]
GO

ALTER TABLE [dbo].[Inspection_CapturedImageItem]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Inspection_CapturedImageItem_dbo.Inspection_CapturedImageQueueItem_ImageRequest_ID] FOREIGN KEY([ImageRequest_ID])
REFERENCES [dbo].[Inspection_CapturedImageQueueItem] ([ID])
GO

ALTER TABLE [dbo].[Inspection_CapturedImageItem] CHECK CONSTRAINT [FK_dbo.Inspection_CapturedImageItem_dbo.Inspection_CapturedImageQueueItem_ImageRequest_ID]
GO

指數

在此處輸入圖像描述

您在該掃描中遇到的性能問題是一個神秘的問題。一般來說，對於不返回任何行的掃描，我希望看到非常相似的 cpu 時間。沒有任何計算列，並且邏輯讀取計數相同。毫不奇怪，我無法在我的機器上重現該問題。也許它與 Express 版有關，但這是一個瘋狂的猜測。

我可以為您提供性能問題的解決方法。查看實際計劃，我們可以看到該Inspection_CapturedImageItem表只有一個不同的值ImageRequest_ID。從該表到表Inspection_CapturedImageQueueItem的聯接是針對錶的主鍵和聚集鍵的，因此使用Inspection_CapturedImageItem外部表的嵌套循環聯接計劃可能非常有效。它肯定比掃描整個Inspection_CapturedImageItem表更有效。

那麼為什麼查詢優化器會選擇合併連接呢？從查詢部分引入的行目標降低了聚集索引掃描成本。TOP (5)事實上，通過一些數學[Extent1].[ImageTaken] = 1) AND ([Extent1].[ImageProjected] <> 1)計算，我可以為您提供過濾器的近似基數估計：

優化器單位的掃描成本 = 0.0031895 + LEAST(1, ROW_GOAL / CARDINALITY_ESTIMATE) * (FULL_SCAN_COST – 0.0031895)

0.0031895 + ( 5 / CARDINALITY_ESTIMATE) * (23.4928 + 1.11635 – 0.0031895) = 0.0453535

CARDINALITY_ESTIMATE = (5 * (23.4928 + 1.11635 – 0.0031895)) / (0.0453535 - 0.0031895)

CARDINALITY_ESTIMATE = 2918

SQL Server 認為大約有 2918 行與Inspection_CapturedImageQueueItem表中的篩選器匹配。您的查詢只需要前五個結果。優化器假定它不必為了這五行掃描整個表。畢竟，表中有 2918 個。不幸的是，您在這裡過濾了表中沒有匹配行的謂詞。這是行目標的最壞情況。您支付了掃描的全部費用，但查詢優化器會創建一個計劃，假設您只需要掃描 0.3% 的表。

有兩種方法可以解決這個問題：您可以為優化器提供更好的資訊，或者您可以強制優化器使用您認為更好的計劃。如果可能，通常首選第一個選項。這裡沒有足夠的細節來深入研究它，但您可能會從ImageTaken和ImageProjected列的統計資訊更新中受益。更新theImageRequest_ID列的統計資訊Inspection_CapturedImageItem也可能是有益的。查詢優化器估計有 42 個不同的行，但只有一個。

如果您確信自己理解為什麼您的計劃不好並且數據不會隨著時間而改變，您可以考慮使用查詢提示。禁用行目標的使用提示HINT('DISABLE_OPTIMIZER_ROWGOAL'), 可能是一個不錯的選擇。通過該提示，您將指示查詢優化器不要優化查詢以盡快返回前五行。相反，將創建一個計劃來有效地返回結果集中的所有行。即使對基數估計不佳，Inspection_CapturedImageItem您也可能最終得到一個嵌套循環連接，它應該比執行掃描的查詢更快。

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

相關問答

Sql-Server

對於在單個表中包含每個使用者數十億行的 sql 數據庫，最有效的設計模式是什麼？

  • February 10, 2022
檢視問答
Sql-Server

SQL Server 2008r2、2017 和 2019 之間的查詢執行異常

  • November 25, 2019
檢視問答
Sql-Server

生產中的查詢慢、執行計劃錯誤或索引錯誤？

  • August 19, 2019
檢視問答
Sql-Server

只讀副本上的長時間執行查詢，在主副本上需要一些時間

  • May 10, 2019
檢視問答
Sql-Server

誰能幫我完成這個糟糕的查詢計劃？

  • April 26, 2019
檢視問答
Sql-Server

相同邏輯的不同表現形式會產生非常不同的執行時間

  • February 16, 2019
檢視問答