Início / dba / Perguntas / 284109
Accepted
Danielle Paquette-Harvey
Asked: 2021-01-28 08:36:24 +0800 CST

Qual é a melhor maneira de encontrar linhas em que uma data e hora está incluída no intervalo de duas colunas de data e hora [duplicado]

  • 772

Estou usando o SQL Server 2016

Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64) 31 de julho de 2020 18:47:07 Copyright (c) Microsoft Corporation Standard Edition (64 bits) no Windows Server 2012 R2 Standard 6.3 (Build 9600) : )

Eu tenho essa tabela que tem dois campos Datetime, um StartDate e um EndDate. Aqui está uma versão simplificada.

CREATE TABLE Seg (
   Id int not null identity(1,1), 
   StartDate datetime not null,
   EndDate datetime not null,
   ModeType tinyint not null,
   TripId int not null,
   constraint Pk_Segs primary key clustered  (Id))

CREATE INDEX [Ix_1] ON [dbo].Seg (ModeType, EndDate, StartDate) include (TripId)
CREATE INDEX [ix_2] ON [dbo].Seg ( [EndDate], [StartDate], [ModeType] ) INCLUDE ( TripId) 
CREATE INDEX Ix_3 ON [dbo].Seg ( [StartDate], [EndDate] ) INCLUDE ( Id) 

INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-11 14:57:33.000', '2019-11-11 19:53:28.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-11 19:53:28.000', '2019-11-11 23:29:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-11 23:29:00.000', '2019-11-12 05:00:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-12 05:00:00.000', '2019-11-13 05:00:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-13 05:00:00.000', '2019-11-14 05:00:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-14 05:00:00.000', '2019-11-14 19:23:49.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-14 19:23:49.000', '2019-11-15 05:00:00.000',1,263938)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:27:54.000', '2019-11-15 22:28:59.450',1,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:28:59.450', '2019-11-15 22:29:13.000',3,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:29:13.000', '2019-11-15 22:29:13.000',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:29:13.000', '2019-11-15 22:31:21.800',1,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:31:21.800', '2019-11-15 22:31:45.000',3,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:31:45.000', '2019-11-15 22:31:46.000',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:31:46.000', '2019-11-15 22:36:56.000',1,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:36:56.000', '2019-11-15 22:37:23.700',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:34:23.000', '2019-11-15 22:42:30.400',1,262853)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-15 22:37:23.700', '2019-11-16 01:20:36.000',3,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:20:36.000', '2019-11-16 01:20:39.750',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:20:39.750', '2019-11-16 01:20:41.000',3,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:20:41.000', '2019-11-16 01:20:42.000',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:20:42.000', '2019-11-16 01:30:09.000',1,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2019-11-16 01:30:09.000', '2019-11-16 01:31:28.150',2,262850)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 18:38:00.000', '2020-12-19 20:12:13.290',1,284621)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 20:12:13.290', '2020-12-19 20:13:30.000',3,284621)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 20:13:30.000', '2020-12-19 21:52:19.250',1,284621)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 22:05:48.000', '2020-12-19 22:08:35.450',1,284622)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-19 22:09:22.000', '2020-12-20 02:26:18.710',1,284620)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-20 02:56:58.000', '2020-12-20 05:00:00.000',1,284619)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-20 05:00:00.000', '2020-12-20 07:06:13.157',1,284619)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-23 13:49:15.000', '2020-12-23 14:05:18.577',1,284586)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-23 15:40:21.000', '2020-12-23 16:01:23.640',1,284587)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-12-23 18:08:36.000', '2020-12-23 18:15:48.300',1,284603)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 13:40:32.540', '2020-08-26 13:44:19.000',3,281725)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 13:44:19.000', '2020-08-26 13:44:49.600',1,281725)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 10:22:24.000', '2020-08-26 17:17:48.300',1,281726)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 17:39:36.000', '2020-08-26 17:42:33.600',1,281727)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 17:43:55.000', '2020-08-26 17:46:14.750',1,281728)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 18:00:21.000', '2020-08-26 18:01:18.500',1,281729)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 18:01:18.500', '2020-08-26 20:35:54.000',3,281729)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 20:35:54.000', '2020-08-26 20:51:27.960',1,281729)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 20:26:57.000', '2020-08-26 20:52:26.560',1,281730)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 17:46:46.000', '2020-08-26 21:08:10.550',1,281731)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 20:53:17.000', '2020-08-26 21:18:17.057',1,281732)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-26 21:59:01.000', '2020-08-27 04:00:00.000',1,281733)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 04:00:00.000', '2020-08-27 04:18:42.063',1,281733)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 06:51:17.000', '2020-08-27 07:43:41.857',1,281734)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 07:52:51.000', '2020-08-27 07:55:01.100',1,281735)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 08:00:06.000', '2020-08-27 09:36:30.210',1,281736)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 13:37:20.000', '2020-08-27 13:51:20.210',1,281737)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 10:51:09.000', '2020-08-27 14:26:19.077',1,281739)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 14:26:19.077', '2020-08-27 14:30:44.000',3,281739)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 14:19:13.000', '2020-08-27 14:31:42.510',1,281738)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 14:30:44.000', '2020-08-27 14:39:14.127',1,281739)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-27 14:47:25.000', '2020-08-27 14:52:59.150',1,281740)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-25 23:03:36.000', '2020-08-27 15:08:29.000',3,281742)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 12:54:53.000', '2020-07-31 18:25:59.000',3,281565)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 18:25:59.000', '2020-07-31 18:32:30.000',3,281566)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 16:48:17.000', '2020-07-31 18:52:14.000',3,281098)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 20:37:33.000', '2020-07-31 20:45:18.850',1,280884)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 21:13:38.000', '2020-07-31 21:15:49.400',1,280885)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 21:20:55.000', '2020-07-31 21:25:23.350',1,280886)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 22:43:12.000', '2020-07-31 23:17:46.900',1,280887)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-07-31 21:39:19.000', '2020-07-31 23:47:38.030',1,280888)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 00:23:50.000', '2020-08-01 04:00:00.000',1,280889)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 04:00:00.000', '2020-08-01 04:59:16.980',1,280889)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 05:14:49.000', '2020-08-01 07:20:22.377',1,280890)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 07:39:50.000', '2020-08-01 07:42:20.650',1,280891)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 07:54:16.000', '2020-08-01 11:18:42.240',1,280892)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 11:18:42.240', '2020-08-01 11:20:43.000',3,280892)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 11:20:43.000', '2020-08-01 11:46:42.987',1,280892)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 13:35:33.000', '2020-08-01 13:44:34.950',1,280893)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 13:54:45.000', '2020-08-01 13:55:59.850',1,280894)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 13:58:03.000', '2020-08-01 14:03:40.350',1,280895)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 14:11:21.000', '2020-08-01 14:13:16.000',1,280896)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 14:28:20.000', '2020-08-01 14:36:25.740',1,280898)
INSERT INTO Seg (StartDate, EndDate, ModeType,TripId) VALUES ('2020-08-01 14:36:25.740', '2020-    08-01 14:36:27.000',3,280898)

A tabela de amostra não é muito grande, mas consigo os mesmos planos de consulta que na produção. Esta tabela tem cerca de 50 milhões de linhas. Temos uma consulta para um relatório que está demorando muito para ser executado.

SELECT s.TripId, s.StartDate
FROM Seg s
WHERE s.StartDate <= '2020-08-01 04:00:00' AND s.EndDate > '2020-08-01 04:00:00' 
AND s.ModeType in (1,2)

A consulta por si só não demora "isso" para ser executada, mas o servidor está muito ocupado e de vez em quando recebo deadlocks com outras consultas do sistema.

Plano de consulta

Eu gostaria de poder remover o predicado e ter apenas um predicado de busca e reduzir o número de leituras porque agora ele lê mais de 3 milhões de linhas para recuperar 155 linhas.

lê

Aqui está o plano de consulta (do banco de dados de produção)

Eu poderia ser capaz de alterar as coisas no código ou no banco de dados para fazer isso funcionar. Qualquer ajuda seria apreciada. Já tentei ajustar o índice ou brincar com a consulta mas não estou chegando a lugar nenhum.

************************ ATUALIZAR ************************* *****************

Eu tentei a consulta proposta por JD

Isso me dá um plano de execução diferente, mas as estimativas ainda estão longe. Aqui está o plano de consulta.

Talvez uma dica de consulta ajude? O nível de compatibilidade do banco de dados é 130.

Eu tentei usar a dica de consulta

OPTION (FAST 100)

As estimativas de cardinalidade são melhores, pois digo basicamente ao SQL para esperar 100 linhas, mas as leituras não mudam. A atualização das estatísticas não muda nada (elas são atualizadas todas as noites).

Talvez não tenha como ter um resultado melhor com uma mesa tão grande?

****************************** EDIT 2 minha solução **************** *****************

Apenas no caso de poder ajudar alguém com o mesmo problema. Limitar o intervalo de datas conforme proposto na segunda solução desta questão resolveu meus problemas. Também vou reescrever a consulta como JD propôs, então marcarei sua resposta como a resposta.

WHERE '2020-08-01 04:00:00' BETWEEN s.StartDate AND s.EndDate AND s.StartDate >= '2020-07-01 04:00:00' AND s.EndDate <= '2020-09-01 04:00:00' ;
sql-server query-performance

1 respostas

  1. Best Answer

    Se você reescrever sua consulta assim, ela melhora?

    SELECT s.TripId, s.StartDate
FROM Seg s
WHERE s.StartDate <= '2020-08-01 04:00:00' AND s.EndDate > '2020-08-01 04:00:00' 
AND s.ModeType = 1

UNION ALL

SELECT s.TripId, s.StartDate
FROM Seg s
WHERE s.StartDate <= '2020-08-01 04:00:00' AND s.EndDate > '2020-08-01 04:00:00' 
AND s.ModeType = 2

    Seu plano de execução está apresentando um grande problema de estimativa de cardinalidade . Ele está estimando 2,5 milhões de linhas a serem retornadas por essa operação de busca de índice quando ela realmente retorna apenas 155 linhas.

    Isso é apenas um palpite de olhar para seus predicados, mas a INpalavra-chave é sintaticamente equivalente a usar OR. Às vezes, o uso de ORlógica baseada pode causar problemas de estimativa de cardinalidade (embora em sua consulta atual seja uma lógica bastante simples, então ainda não estou convencido de que é o problema).

    A consulta acima deve ser logicamente equivalente à anterior, mas pode corrigir seu problema de estimativa de cardinalidade por ser uma solução mais baseada em conjunto e deve gerar pelo menos um plano de execução diferente .

    • 2

relate perguntas