CODEWITHSUNDEEP
sql-serverazure
David Hawkins
David Hawkins

Reputation: 1067

SQL Server / Azure DB - adding an JOIN slows query by seconds for only 180~ rows

I have the following:

Query:

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

Execution Plan

enter image description here

Related Schema

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]);

Row Counts

AnprEventCount: 5676

BidReviewCaseCount: 176

CameraCount: 2

Problem

The above query takes 3-4 seconds and by removing the camera JOIN it then takes about 5ms

I discovered I can change my JOIN to LEFT JOIN and it fixes the issue. However I am querying this using EntityFramework which will be using INNER JOINS.

I have no idea what is causing this behaviour. Any advice in resolving this issue would be greatly appreciated.

Upvotes: 1

Views: 92

Answers (1)

Maxim Balaganskiy
Maxim Balaganskiy

Reputation: 1574

Please try updating statistics on tables involved with update statistics command.

The thing is the engine righteously thinks that it's cheaper to join smaller tables first and then join the result with the dependent table. Add an index on anpreventid field, this should speed up the query

Upvotes: 3

Related Questions