Order by performance issue on joined tables

I have two tables: "Alarms" and "Devices" and a view "vwAlarms" Alarms Table has 250K rows and Devices Table has only 50 rows.

vwAlarms is just join of two table.

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.

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 view 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 execution plan:

Warning message of of sort:

Alarm table Schema:

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 

---

Is using the view a must? If not, you probably should first get your 100 alarms and then join with devices. That's the end result you want right?

---

It is not being smart about the sort.
Do you have the FK defined?

I would try all option of the table hints on the join to try and push that sort earlier.

Join Hints (Transact-SQL)

If table hints does not work I would try a Cross Apply.
I think the Cross Apply should be smart about sort.
But at the cost of not being as fast on the "join".
So it would be good at returning the first 1000 or 10,000 but bad at all.

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

链接地址: http://www.djcxy.com/p/44010.html

上一篇: SQL Server操作数顺序

下一篇: 按连接表上的性能问题排序