sql - 获取上次更改值时的日期时间的高性能查询
问题描述
我正在使用的数据
考虑以下 2 个数据库表:
CREATE TABLE [dbo].[Contact](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Contact_UID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Contact_Contact_UID] DEFAULT (newsequentialid()),
[Name] [nvarchar](255) NOT NULL,
[ContactStatus] [nvarchar](255) NOT NULL)
CREATE TABLE [dbo].[Contact_Log](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LogDate] [datetimeoffset](7) NOT NULL CONSTRAINT [DF_Contact_Log_LogDate] DEFAULT (sysdatetimeoffset()),
[Contact_UID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Contact_Log_Contact_UID] DEFAULT (newsequentialid()),
[Name] [nvarchar](255) NOT NULL,
[ContactStatus] [nvarchar](255) NOT NULL)
Contact 表是联系人记录的主表。它存储联系人的姓名和状态(例如“活着”、“死亡”或其他)。
Contact_Log 表存储对 Contact 表所做的所有更改。
所以这里有一些示例数据:
接触:
+----+--------------------------------------+------+---------------+
| ID | Contact_UID | Name | ContactStatus |
+----+--------------------------------------+------+---------------+
| 1 | 62918AC1-1C6C-4DEB-B7F8-5D5EF913F667 | John | Dead |
+----+--------------------------------------+------+---------------+
| 2 | F7844037-2FF5-47B9-874D-C0920E7DC092 | Jane | Alive |
+----+--------------------------------------+------+---------------+
联系方式_日志:
+----+--------------------------------------+------+---------------+------------+
| ID | Contact_UID | Name | ContactStatus | LogDate |
+----+--------------------------------------+------+---------------+------------+
| 1 | 62918AC1-1C6C-4DEB-B7F8-5D5EF913F667 | John | Alive | 2019-01-01 |
+----+--------------------------------------+------+---------------+------------+
| 2 | 62918AC1-1C6C-4DEB-B7F8-5D5EF913F667 | John | Dead | 2019-01-02 |
+----+--------------------------------------+------+---------------+------------+
| 3 | 62918AC1-1C6C-4DEB-B7F8-5D5EF913F667 | John | Dead | 2019-01-03 |
+----+--------------------------------------+------+---------------+------------+
| 4 | F7844037-2FF5-47B9-874D-C0920E7DC092 | Jane | Alive | 2019-01-04 |
+----+--------------------------------------+------+---------------+------------+
注意:此时我还没有在该表上添加任何索引或类似的东西。
测试场景
以上只是一些示例数据。我正在测试的数据具有以下行数:
联系人:~10,000 行
Contact_Log:~3,000,000 行
我目前正在使用 SQL Server 2008 R2 进行测试。因此,首选在此及以后支持的解决方案。
我想要达到的目标
基本上,我正在尝试制定一个查询,该查询可以告诉我上次更改字段的LogDate
时间ContactStatus
,对于特定的Contact_UID
,取自Contact_Log
表。
例如,如果我感兴趣的记录是“John”,那么结果应该是“2019-01-02”。因为这是 John 的 ContactStatus 上次更改的日期(即从“Alive”更改为“Dead”)。
最终,我想把这个查询放到一个函数中。一个可以通过传入 Contact_UID 和我要检查的字段的名称来调用的函数。然后可以将此函数作为更一般查询的一部分调用。例如:
SELECT Name, MyFunction('62918AC1-1C6C-4DEB-B7F8-5D5EF913F667', 'ContactStatus') AS StatusLastChanged FROM Contact
到目前为止我尝试过的
好吧,我已经尝试了一些东西,虽然我可以得到我想要的结果。我的尝试确实在与性能问题作斗争。
注意:虽然我真的只想要一个 datetimeoffset 结果。一些尝试包括更多数据/字段,只是为了尝试验证数据是否准确。
尝试1:
SELECT TOP(1) a.LogDate
FROM Contact_Log AS a
WHERE a.Contact_UID = '62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
AND a.ContactStatus <>
(
SELECT TOP(1) b.ContactStatus
FROM Contact_Log AS b
WHERE b.Contact_UID = '62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
AND a.LogDate > b.LogDate
ORDER BY b.LogDate DESC
)
ORDER BY LogDate DESC
问题1:太慢了。在等待近一个小时没有结果后,我不得不停止查询。
尝试2:
SELECT A.LogDate
FROM (SELECT ROW_NUMBER() OVER (ORDER BY LogDate DESC, ID DESC) AS rnum, ID, LogDate, Contact_UID, ContactStatus FROM Contact_Log) A
LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY LogDate DESC, ID DESC) AS rnum, ID, LogDate, Contact_UID, ContactStatus FROM Contact_Log) B
ON A.rnum = B.rnum-1
WHERE
(B.rnum IS NULL
OR (A.Contact_UID = '62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
AND B.Contact_UID = '62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
AND A.ContactStatus != B.ContactStatus))
ORDER BY A.rnum
问题 2:这可行,并为我提供了正确的数据集。但是,它需要 6 秒,这太慢了。请记住,它需要在更一般的查询(大约 10,000 行)中作为函数工作。
尝试3:现在这和尝试2基本相同,期待我尝试申请TOP(1)
以获得我真正想要的结果。
SELECT TOP(1) A.LogDate
FROM (SELECT ROW_NUMBER() OVER (ORDER BY LogDate DESC, ID DESC) AS rnum, ID, LogDate, Contact_UID, ContactStatus FROM Contact_Log) A
LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY LogDate DESC, ID DESC) AS rnum, ID, LogDate, Contact_UID, ContactStatus FROM Contact_Log) B
ON A.rnum = B.rnum-1
WHERE
(B.rnum IS NULL
OR (A.Contact_UID = '62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
AND B.Contact_UID = '62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
AND A.ContactStatus != B.ContactStatus))
ORDER BY A.rnum
问题 3:令我惊讶的是,这比尝试 2 花费的时间要长得多,尽管我所做的只是TOP(1)
在开始时添加。这花了 5 多分钟,所以我停止了查询并放弃了。
问题
我怎样才能在“我正在努力实现的目标”中做我想做的事,但又能达到合理的表现?(我很乐意在这个阶段把它控制在 1 秒以内)。
请记住,我只想要一个 datetimeoffset 作为结果,以便可以在函数中使用它。
到目前为止,我还没有创建特定的索引。如果无法改进查询,我很乐意将这些建议视为合适的答案。或对架构进行任何适当的更改。
底线
我正在寻找一个将产生 1 个结果的查询,其中包含 1 个 datetimeoffset 字段。它需要不到 1 秒的时间来运行。
解决方案
您希望选择紧接在不等于当前 ContactStatus 的最高日期之后的最小日期。那将是这样的:
select
min(LogDate)
from Contact_Log
where
Contact_UID='62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
and ContactStatus = (
select top 1
ContactStatus
from Contact_Log where
Contact_UID='62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
order by Log_Date desc
)
and LogDate > (
select max(LogDate)
from Contact_Log
where Contact_UID='62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
and ContactStatus != (
select top 1
ContactStatus
from Contact_Log where
Contact_UID='62918AC1-1C6C-4DEB-B7F8-5D5EF913F667'
order by Log_Date desc
)
);
推荐阅读
- javascript - 如何在异步函数之外返回我用 pupeteer 抓取的值
- typescript - 类型“this[K]”不可分配给类型“NonNullable”
' - excel - Vlookup 的可变起点
- azure - DataFactory 每隔 1 分钟从 DataLake 复制一次数据
- c++ - 确定是否存在没有功能测试宏的类型
- python - 在 DataFrame Pandas 中,我如何按天过滤 timedelta64 并将值更改为 str?
- swift - 从故事板返回主机控制器到另一个控制器
- python - 熊猫数据框中列的有效小计
- pandas - 如何将数据框的含义分配给特定的数据框行?
- excel - 从所有表中获取所有值