Início / dba / Perguntas / 228876
Accepted
WadeH
Asked: 2019-02-05 10:53:48 +0800 CST

Ajuda de índice para consulta ruim em tabela bastante grande (2m)

  • 772

Procuro ajuda por favor. Eu tenho uma consulta que está sendo executada em uma tabela bastante grande (2 milhões de registros).

Eu tenho tentado fazer com que os índices funcionem de forma eficiente. Existem algumas outras consultas nesta tabela, mas esta é de longe a mais frequente. Estou seriamente lutando para executá-lo em menos de 1 segundo e geralmente o vejo sendo executado em 3 a 5 segundos usando o profiler.

Pode ser o mais rápido possível, mas eu gostaria de receber algumas informações para confirmar/negar isso, por favor.

Apenas uma observação: o desenvolvedor não alterará a consulta ou o esquema. As otimizações podem ser feitas apenas no banco de dados e nenhuma alteração de esquema.

A mesa:

CREATE TABLE [dbo].[Notifications](
[ntID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[NotificationID] [int] NOT NULL,
[NotificationType] [nvarchar](50) NOT NULL,
[UserName] [nvarchar](50) NULL,
[CreatedBy] [nvarchar](50) NULL,
[CreatedOn] [datetime] NULL,
[Status] [nvarchar](50) NOT NULL,
[Result] [nvarchar](50) NULL,
[Extension] [nvarchar](50) NULL,
[ShiftRate] [nvarchar](255) NULL,
[ResponseMinutes] [int] NULL,
[ResponseWindow] [datetime] NULL,
[caNotificationID] [int] NULL,
[AwardedBy] [nvarchar](50) NULL,
[AwardedOn] [datetime] NULL,
[CancelledBy] [nvarchar](50) NULL,
[CancelledOn] [datetime] NULL,
[CancelledReasonID] [int] NULL,
[CancelledReasonText] [nvarchar](255) NULL,
[AwardingDate] [datetime] NULL,
[ScheduledLaunchDate] [datetime] NULL,
[CustomMessage] [nvarchar](160) NULL,
[SystemName] [nvarchar](4000) NULL,
[AutoClose] [bit] NOT NULL,
 CONSTRAINT [PK_ESP_Notifications_ntID] PRIMARY KEY CLUSTERED 
(
    [ntID] 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].[ESP_Notifications] ADD  DEFAULT ((0)) FOR [AutoClose]
GO

Instantâneo de dados da tabela:

captura de tela de dados de amostra

A pergunta:

Update Notifications 
  set Status = 'Awarding' OUTPUT deleted.* 
where ntID = (
    select top(1) ntID 
    from Notifications 
    where NotificationType = 'Shift' 
      and (Status = 'Done') 
      and ResponseWindow < '2019-02-04 10:40:03' 
    order by ntID)

Índices tentados:

CREATE INDEX [IX_Notifications_Status_NotificationType_ResponseWindow_ntID] 
ON [dbo].[Notifications](
   [Status] ASC,[NotificationType] ASC,[ResponseWindow] DESC,[ntID] DESC
)

CREATE INDEX [IX_Notifications_Status_ScheduledLaunchDate_ntID] 
ON [dbo].[Notifications](
    [ScheduledLaunchDate] DESC,[Status] ASC,[ntID] ASC
)

CREATE INDEX [IX_Notifications_NotificationType_caNotificationID_NotificationID_ntID] 
ON [dbo].[Notifications](
   [NotificationType] DESC, [caNotificationID] DESC, [NotificationID] DESC, [ntID] DESC
);

NotificationType contém 3 tipos diferentes, dos quais 70% são do tipo 'Shift' Status tem 10 tipos, mas os registros 'In Flight' são apenas cerca de 100 a 200, divididos em 4 Status's

Obrigado pela sua ajuda.

sql-server index

1 respostas

  1. Best Answer

    Se a subconsulta nessa atualização usar consistentemente esses dois valores de predicado, um índice filtrado deve ajudar muito. Algo assim (que Erik Darling gentilmente forneceu como comentário):

    CREATE INDEX IX_ntID_ResponseWindow_Includes ON dbo.Notifications (ntID, ResponseWindow) 
INCLUDE (NotificationType, Status) 
WHERE (Status = 'Done' AND NotificationType = 'Shift');

    Isso permitirá que a subconsulta encontre o grupo relevante de IDs que correspondem a Statusand NotificationType(já que são as únicas linhas no índice) e evitará classificar os dados (já que já estão ordenados por ntID como a coluna principal no índice).

    Ele ainda terá que verificar o ResponseWindowvalor em cada linha. Na pior das hipóteses, se não houver linhas que atendam ao requisito de data para a consulta (ou se a primeira correspondência tiver um ntID muito alto), a maior parte ou todo o índice será lido. O que torna a eficácia dessa abordagem limitada dependendo da distribuição dos dados.

    • 15

relate perguntas