优化SQL查询，查找不存在于表中的条目

Question

我有一个日志表，其中包含作业状态。其中一列是作业名称。有时作业根本不会运行，因此没有记录。我的SQL确定哪些作业在日志中“丢失”。

这个查询大约需要17分钟，非常漫长。我有很多其他更复杂的查询（至少看起来更复杂），但没有这么慢。

如何进行优化？

-- Display missing jobs. Thats jobs that are not in job log but should be
declare @startDate datetime, @endDate datetime
declare @rangeInHours int
set @rangeInHours = -24
set @endDate = '2012-01-17 12:00:01'
set @startDate = dateadd(hour, @rangeInHours, @endDate)

declare @myTable table( name nvarchar(50))
insert into @myTable values('Activity work')
-- There are another 100 entries like this one above to add all the expected jobs

-- this is my sql to find missing jobs
select distinct i.name from @myTable i
where not exists
 ( select 1 from job_log j
     where j.name = i.name 
       and j.start_date > @startDate and j.start_date < @endDate
  )
order by i.name asc

我浏览了很多帖子，但没有找到合适的答案，至少是我能理解并在我的有限SQL中实现的答案。

- Jimbydude

---

你在job_log中有适当的索引吗？特别是(name，start_date)的覆盖索引。你能发布查询计划吗？ - Lieven Keersmaekers

job_log中有多少条记录？查询看起来很正常，所以像Lieven说的那样，我认为这将是一个索引问题。 - Gary - Stand with Ukraine

Answer 1

你可以尝试下面的代码，它可能比嵌套选择更快。

select distinct m.name
from @myTable m
left outer join job_log j on j.name = m.name and j.start_date > @startDate and j.start_date < @endDate
where j.name is null

- RobJohnson

---

我尝试了你的建议，我的查询时间从17分钟缩短到了10秒！太棒了！也想感谢其他所有人给出的有用和非常快速的回复。很抱歉，我还不能投票支持任何人，否则我一定会这么做的。 - Jimbydude

Answer 2

和大多数SQL问题一样，答案在于您的模式，而不是查询。要检查是否存在名称为@name且start_date在@start和@end之间的作业，则必须在（名称，start_date）上拥有索引：

create index job_log_name_start on (name, start_date);

作为一般规则，当询问SQL问题时，请始终发布涉及的表的确切定义，包括所有索引。job_log的聚集索引键是什么？由于大多数查询请求时间范围，因此时间序列通常按时间键聚集，这可能会使您的job_log表按start_date进行聚集。

对于更高级的功能，“这个条目肯定不存在吗？”的普遍解决方案是通过Bloom过滤器回答的，我已经看到了基于SQL的实现（例如对名称和作业小时进行哈希处理）。

- Remus Rusanu

---

在评论中我问的差不多了，但是user1154526可能已经离开了<g>。Bloom过滤器的参考值+1。 - Lieven Keersmaekers

Answer 3

请确保在 job_log.name 上有索引。

DECLARE @rangeInHours int = -24; 
DECLARE @endDate datetime = '2012-01-17 12:00:01';
DECLARE @startDate datetime = DATEADD(hour, @rangeInHours, @endDate);    
DECLARE @ExpectedJobs table(name nvarchar(50));

INSERT INTO @ExpectedJobs values('Activity work'), ('foo'), ('bar');

SELECT      e.name AS MissingJob
FROM        @ExpectedJobs AS e
LEFT JOIN   job_log AS j ON j.name = e.name 
WHERE
    j.[name] IS NULL
AND j.start_date > @startDate 
AND j.start_date < @endDate
GROUP BY e.name
ORDER BY e.name;

- p.campbell