主页 / dba / 问题 / 284109
Accepted
Danielle Paquette-Harvey
Asked: 2021-01-28 08:36:24 +0800 CST

查找日期时间包含在两个日期时间列范围内的行的最佳方法是什么[重复]

  • 772

我正在使用 SQL Server 2016

Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64) 2020 年 7 月 31 日 18:47:07 版权所有 (c) Windows Server 2012 R2 Standard 6.3 (Build 9600) 上的 Microsoft Corporation 标准版（64 位） :)

我有这个表，它有两个日期时间字段，一个 StartDate 和一个 EndDate。这是一个简化版本。

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)

示例表不是很大，但我实现了与生产中相同的查询计划。该表有大约 5000 万行。我们有一个查询需要很长时间才能运行的报告。

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)

查询本身不会花费“那么”长的时间来执行，但是服务器非常繁忙，并且有时我会遇到来自系统的其他查询的死锁。

查询计划

我希望能够删除谓词并且只有一个查找谓词，并减少读取次数，因为现在它读取超过 300 万行来检索 155 行。

读

这是查询计划（来自生产数据库）

我可以更改代码或数据库中的内容以使其正常工作。任何帮助，将不胜感激。我已经尝试微调索引或使用查询，但我没有得到任何结果。

************************ 更新 ************************* *****************

我试过JD提出的查询

它确实给了我一个不同的执行计划，但估计还有很长的路要走。这是查询计划。

也许查询提示会有所帮助？数据库兼容级别为 130。

我尝试使用查询提示

OPTION (FAST 100)

基数估计更好，因为我基本上告诉 SQL 期望 100 行，但读取不会改变。更新统计数据不会改变任何东西（它们每晚都会更新）。

这么大的桌子可能没有更好的结果？

******************************* 编辑 2 我的解决方案 *************** *****************

以防万一它可以帮助有同样问题的人。按照这个问题的第二个解决方案中提出的限制日期范围解决了我的问题。我还将按照 JD 的建议重新编写查询，因此我会将他的答案标记为答案。

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 个回答

  1. Best Answer

    如果您像这样重新编写查询，它会变得更好吗？

    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

    您的执行计划显示出巨大的 基数估计问题。当它实际上只返回 155 行时，它估计该索引查找操作将返回 250 万行。

    这只是查看谓词的猜测，但IN关键字在语法上等同于 using OR。有时使用OR基于逻辑可能会导致基数估计问题（尽管在您当前的查询中它是相当简单的逻辑，所以我还没有出售它是问题）。

    上面的查询在逻辑上应该与您之前的查询等效，但可以通过作为一个更加基于集合的解决方案来解决您的基数估计问题，并且它至少应该生成不同的执行计划。

    • 2

相关问题