按连接表上的性能问题排序

我有两个表格：“报警”和“设备”和视图“vwAlarms”报警表具有250K行，而设备表格只有50行。

vwAlarms只是两个表的连接。

我的问题是当我添加Top（x）并按ID desc命令从vwAlarm中选择*时，需要10秒钟来执行查询。 然而相同的查询在表格报警上快速运行。

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

这是我的观点定义：

  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

这是执行计划：

排序的警告信息：

报警表架构：

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 

---

使用视图必须？ 如果没有，你可能应该先获得100个警报，然后加入设备。 这是你想要的最终结果？

---

这种情况并不明智。
你有没有定义FK？

我会尝试连接上的表提示的所有选项，以尝试更早的推送。

加入提示（Transact-SQL）

如果表提示不起作用，我会尝试一个交叉应用。
我认为Cross Apply应该在排序方面很聪明。
但代价是在“加入”方面没有那么快。
所以它会很好地返回前1000或10,000但是很糟糕。

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

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

上一篇: Order by performance issue on joined tables

下一篇: Index with Leftouter join there is always Index scan in sql server 2005