StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOrder by performance issue on joined tables
text
Body
copied!<p>I have two tables: "Alarms" and "Devices" and a view "vwAlarms" Alarms Table has 250K rows and Devices Table has only 50 rows.</p> <p>vwAlarms is just join of two table.</p> <p>my problems is when i add Top(x) and order by id desc to select * from vwAlarm it takes 10 seconds to execute query. however the same query runs quickly on table Alarm.</p> <pre><code>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 </code></pre> <p>Here is my view definition:</p> <pre><code> 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 </code></pre> <p>Here is execution plan: <img src="https://i.stack.imgur.com/OPvNP.png" alt="enter image description here"></p> <p>Warning message of of sort: <img src="https://i.stack.imgur.com/A3zfZ.png" alt="enter image description here"></p> <p>Alarm table Schema:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload