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

i have following:

query:

set statistics time on; go  select *  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

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 , removing camera join takes 5ms

i discovered can change join left join , fixes issue. querying using entityframework using inner joins.

i have no idea causing behaviour. advice in resolving issue appreciated.

please try updating statistics on tables involved update statistics command.

the thing engine righteously thinks it's cheaper join smaller tables first , join result dependent table. add index on anpreventid field, should speed query