Pivot table performance order

Question

I have two tables: "Alarms" and "Devices" and the view "vwAlarms", the Alarm table contains 250 thousand rows, and the table "Devices" has only 50 rows.

vwAlarms just joins two tables.

my problems - when I add Top (x) and order by id desc to select * from vwAlarm, it takes 10 seconds to complete the request. however, the same query is executed quickly in the Alarm table.

select * from Alarm --in milliseconds. select * from vwAlarms --in milliseconds select top (100) * from Alarms order by id desc --in milliseconds select top (100) * from vwAlarms order by id desc --takes 10 seconds 

Here is my definition:

  CREATE VIEW [dbo].[vwAlarms] AS SELECT dbo.Devices.Id , dbo.Devices.Name , dbo.Devices.PortsTagPrefix , dbo.Devices.ControlCenterNumber , dbo.Devices.AlarmNumber1 , dbo.Devices.AlarmNumber2 , dbo.Devices.SimCardNumber , dbo.Devices.StationNumber , dbo.Devices.SlaveId , dbo.Devices.TypeId , dbo.Devices.RegionId , dbo.Devices.EnquiryPassword , dbo.Devices.SetupPassword , dbo.Devices.ProtocolId , dbo.Devices.UploadedPacketsCount , dbo.Devices.LastPort , dbo.Devices.LastIp , dbo.Devices.IsForTesting , dbo.Devices.Latitude , dbo.Devices.Longitude , dbo.Devices.X , dbo.Devices.Y , dbo.Devices.MainSchematicId , dbo.Devices.MainTimeChartId , dbo.Devices.MainCategoryChartId , dbo.Alarms.Id , dbo.Alarms.DeviceId , dbo.Alarms.LogId , dbo.Alarms.PortId , dbo.Alarms.TypeId , dbo.Alarms.DateTime , dbo.Alarms.AcknowledgerId , dbo.Alarms.AcknowledgeDateTime , dbo.Alarms.Acknowledged , dbo.Alarms.PortValue FROM Devices INNER JOIN Alarms ON Devices.Id = Alarms.DeviceId ORDER BY dbo.Alarms.Id DESC 

Here is the execution plan:

Warning message type:

Alarm Table:

 CREATE TABLE [dbo].[Alarms]( [Id] [int] IDENTITY(1,1) NOT NULL, [DeviceId] [int] NOT NULL, [LogId] [int] NOT NULL, [PortId] [int] NOT NULL, [TypeId] [int] NOT NULL, [DateTime] [datetime2](0) NOT NULL, [AcknowledgerId] [int] NULL, [AcknowledgeDateTime] [datetime2](0) NULL, [Acknowledged] [bit] NULL, [PortValue] [numeric](19, 4) NULL, CONSTRAINT [PK_Alarms] 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].[Alarms] WITH CHECK ADD CONSTRAINT [FK_Alarms_AlarmTypes] FOREIGN KEY([TypeId]) REFERENCES [dbo].[AlarmTypes] ([Id]) GO ALTER TABLE [dbo].[Alarms] CHECK CONSTRAINT [FK_Alarms_AlarmTypes] GO ALTER TABLE [dbo].[Alarms] WITH CHECK ADD CONSTRAINT [FK_Alarms_Devices] FOREIGN KEY([DeviceId]) REFERENCES [dbo].[Devices] ([Id]) GO ALTER TABLE [dbo].[Alarms] CHECK CONSTRAINT [FK_Alarms_Devices] GO ALTER TABLE [dbo].[Alarms] WITH CHECK ADD CONSTRAINT [FK_Alarms_ExtendedUsers] FOREIGN KEY([AcknowledgerId]) REFERENCES [dbo].[ExtendedUsers] ([Id]) GO ALTER TABLE [dbo].[Alarms] CHECK CONSTRAINT [FK_Alarms_ExtendedUsers] GO ALTER TABLE [dbo].[Alarms] WITH CHECK ADD CONSTRAINT [FK_Alarms_Logs] FOREIGN KEY([LogId]) REFERENCES [dbo].[Logs] ([Id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER 

Answer 1

This is not a skill like that.
Do you have FK?

I would try all the table hint options in the join to try to push this type earlier.

Merge Tooltips (Transact-SQL)

If the table hints do not work, I will try applying Cross Apply. I think Cross Apply should be smart about.
But the price is not so quick to "join."
So it would be nice to return the first 1,000 or 10,000, but generally bad.

 SELECT t1.*, t2o.* FROM t1 CROSS APPLY ( SELECT * FROM t2 WHERE t2.t1_id = t1.id ) t2o 

Answer 2

Is use of a view mandatory? If not, you should probably get 100 alarms first and then join the devices. Is this the end result you need?