sql-server - SQL Server/Azure DB - 添加 JOIN 只会使 180~ 行的查询速度减慢几秒

Question

我有以下内容:

查询:

SET STATISTICS TIME ON;
GO

SELECT * 
FROM dbo.BidReviewCase
JOIN dbo.AnprEvent ON AnprEvent.Id = BidReviewCase.AnprEventId
JOIN dbo.Camera ON Camera.Id = AnprEvent.CameraId -- << SLOW JOIN HERE
SET STATISTICS TIME OFF

执行计划

相关架构

CREATE TABLE [dbo].[BidReviewCase] (
    [CaseId]                   INT              IDENTITY (1, 1) NOT NULL,
    [AnprEventId]              INT              NOT NULL,
    [Summary]                  NVARCHAR (MAX)   NULL,
    CONSTRAINT [PK_BidReviewCase] PRIMARY KEY CLUSTERED ([CaseId] ASC),
    CONSTRAINT [FK_BidReviewCase_AnprEvent_AnprEventId] FOREIGN KEY ([AnprEventId]) REFERENCES [dbo].[AnprEvent] ([Id]) ON DELETE CASCADE
);

CREATE TABLE [dbo].[Camera] (
[Id]          INT              IDENTITY (1, 1) NOT NULL,
[CameraName]  NVARCHAR (MAX)   NOT NULL,
[SiteId]      INT              NOT NULL,
[NvrIp]       NVARCHAR (MAX)   NULL,
[NvrPassword] NVARCHAR (MAX)   NULL,
[NvrPort]     NVARCHAR (MAX)   NULL,
[NvrUsername] NVARCHAR (MAX)   NULL,
[ChannelA]    NVARCHAR (MAX)   NULL,
[ChannelB]    NVARCHAR (MAX)   NULL,
[Reference]   UNIQUEIDENTIFIER DEFAULT ('00000000-0000-0000-0000-000000000000') NOT NULL,
CONSTRAINT [PK_Camera] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Camera_Site_SiteId] FOREIGN KEY ([SiteId]) REFERENCES [dbo].[Site] ([Id]) ON DELETE CASCADE
);

CREATE TABLE [dbo].[AnprEvent] (
[Id]              INT              IDENTITY (1, 1) NOT NULL,
[DateTimeRead]    DATETIME2 (7)    NOT NULL,
[Reference]       UNIQUEIDENTIFIER NOT NULL,
[Registration]    NVARCHAR (MAX)   NOT NULL,
[CameraId]        INT              DEFAULT ((0)) NOT NULL,
[Country]         INT              DEFAULT ((0)) NOT NULL,
[DateTimeCreated] DATETIME2 (7)    NULL,
[Direction]       INT              DEFAULT ((0)) NOT NULL,
[EventReference]  UNIQUEIDENTIFIER DEFAULT ('00000000-0000-0000-0000-000000000000') NOT NULL,
[LaneId]          INT              DEFAULT ((0)) NOT NULL,
[Region]          INT              DEFAULT ((0)) NOT NULL,
CONSTRAINT [PK_AnprEvent] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_AnprEvent_Camera_CameraId] FOREIGN KEY ([CameraId]) REFERENCES [dbo].[Camera] ([Id]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [AnprEventCameraId]
ON [dbo].[AnprEvent]([CameraId] ASC)
INCLUDE([Id]);

行数

AnprEventCount:5676

出价审核案例数:176

相机数量:2

问题

上述查询需要 3-4 秒，如果删除相机 JOIN，则需要大约 5ms

我发现我可以将 JOIN 更改为 LEFT JOIN 并且它解决了问题。不过，我正在使用 EntityFramework 查询此内容，它将使用 INNER JOINS。

我不知道是什么导致了这种行为。任何解决此问题的建议将不胜感激。

Answer 1

请尝试更新更新统计信息命令涉及的表的统计信息。

问题是引擎正确地认为首先连接较小的表然后将结果与依赖表连接起来更便宜。在 anpreventid 字段上添加索引，这应该会加快查询速度