sql-server - DATE 和 TINYINT 之间的性能差异
问题描述
2 个类似的查询导致执行时间完全不同。
我有 2 个类似的表。它们之间的唯一区别是一个包含 DATE 数据类型,另一个包含 TINYINT。这是表格的样子。它们是临时表,主键是唯一的索引。
CREATE TABLE #Carrier_DOB(
Carrier_Record_ID INT NOT NULL PRIMARY KEY CLUSTERED,
DOB_DT DATE NOT NULL,
Cohort_Person_ID INT NULL
)
CREATE INDEX IX_Carrier_DOB ON #Carrier_DOB(Cohort_Person_ID)
CREATE TABLE #Carrier_Race(
Carrier_Record_ID INT NOT NULL PRIMARY KEY CLUSTERED,
BENE_RACE_CD TINYINT NOT NULL,
Cohort_Person_ID INT NULL
)
CREATE INDEX IX_Carrier_Race ON #Carrier_Race(Cohort_Person_ID)
以下是通过 sp_spaceused 获得的内容:
name rows reserved data index_size unused
#Carrier_DOB 75836908 2038528 KB 2029088 KB 7456 KB 1984 KB
#Carrier_Race 72139975 1647744 KB 1639552 KB 6016 KB 2176 KB
以下是我很好奇的查询:
--Query1
SELECT *
INTO #DOB_Differences
FROM #Carrier_DOB
WHERE Cohort_Person_ID IN (
SELECT a.Cohort_Person_ID FROM #Carrier_DOB a
INNER JOIN #Carrier_DOB b
ON b.Cohort_Person_ID = a.Cohort_Person_ID
AND b.DOB_DT <> a.DOB_DT
GROUP BY a.Cohort_Person_ID ) --466464 rows in 19:30
--Query2
SELECT #
INTO #Race_Differences
FROM #Carrier_Race
WHERE Cohort_Person_ID IN (
SELECT a.Cohort_Person_ID FROM #Carrier_Race a
INNER JOIN #Carrier_Race b
ON b.Cohort_Person_ID = a.Cohort_Person_ID
AND b.BENE_RACE_CD <> a.BENE_RACE_CD
GROUP BY a.Cohort_Person_ID ) -- 3476884 rows in 0:13
这两个查询之间的主要区别在于,一个是比较 DATE 字段,另一个是比较 CHAR(1)。我很惊讶 Query1 需要 19 分钟,而 Query2 只需要 13 秒。这是正常的吗?我通常不处理 7500 万行表,所以我以前从未注意到这一点。我没有添加更多索引,因为这只是我过程中的一步。以下是查询计划。它们完全不同,但我不明白为什么。这是实际的查询计划加上我添加的索引,如上所示。索引将两个查询的时间缩短到 12:24 分钟,但我仍然不明白为什么 Query1 比 Query1 慢得多。
解决方案
根据计划,看起来正在发生的事情是 tinyint 的行为就像它可以在日期不能的地方进行哈希连接。这对我来说很有意义,因为 tinyint 非常小,基本上只有哈希的大小,可以用作本机哈希。这就是我认为正在发生的事情。
如果我是对的,解决起来很简单,只需在第一个表中的日期字段上创建一个非唯一索引。应该快很多。
只是指出如果目标是找到具有多个这些值的项目,您可以使用 group by 并拥有 - 像这样:
SELECT COUNT(*)
FROM #Carrier_DOB
GROUP BY Cohort_Person_ID
HAVING COUNT(DISTINCT DOB_DT) > 1
推荐阅读
- docker - Kubernetes、Helm 和 Rancher(以及其他)之间的简短事实区别是什么
- javascript - 删除javascript中url中第二个点之后的子字符串
- python - 用较小的列表替换较大列表的一部分
- asp.net - 如何将 UrlReffer 从 .net 替换为 .netCore
- spring - 如何在 SecurityConfig 类中配置内容安全策略
- react-native - React Native 垂直对齐一行 3 个项目
- node.js - 如何使用 Webdriver IO 填充登录提示?
- javascript - 数据选项卡上一个/下一个按钮
- php - 打印出 JSON 对象
- tensorflow - 创建一个简单的 TensorFlow Keras.sequential 模型并接收所有相同类的结果