sql-server - TSQL - 返回所有记录 ID 和我们需要填充的缺失 ID

Question

我有一项需要自动化的工作，以确保填充数据库中某些实体的缓存。我使用以下查询 CTE和 CROSS JOIN但它运行得不是很快，所以我相信它可以改进。

问题:

我有一个员工数据库

每个员工每个月都有一份数据报告。

每个报告都有一组“组件”，每个组件的“数据”都从外部源中提取并缓存在我的数据库中

目标:

我想设置一个作业来为“本月报告”获取一组组件 ID，并在数据不存在时预缓存数据。

我需要获取一份员工列表以及他们在本月报告的缓存中丢失的组件。然后我将设置一个 CRON 作业来处理队列。

问题

我下面的查询很慢 - 有没有更有效的方法来返回缓存中缺少的员工列表和组件 ID？

当前 SQL:

declare @reportDate datetime2 = '2019-10-01'; //the report publish date
declare @componentIds table (id int);         // store the ids of each cachable component
insert @componentIds(id) values(1),(2),(3),(4),(5);   

;WITH cteCounts 
AS (SELECT r.Id as reportId, cs.componentId,
   COUNT(1) AS ComponentCount
   FROM EmployeeReports r
   LEFT OUTER JOIN CacheStore cs on r.Id = cs.reportId and cs.componentId in (SELECT id FROM @componentIds)
   GROUP BY r.Id, cs.componentId)

SELECT e.Id, e.name, _c.id as componentId, r.Id as reportId
FROM Employees e
INNER JOIN EmployeeReports r on e.Id = r.employeeId and r.reportDate = @reportDate 
CROSS JOIN @componentIds _c
LEFT OUTER JOIN cteCounts AS cn
    ON _c.Id = cn.componentId AND r.Id = cn.reportId
WHERE cn.ComponentCount is null

Answer 1

我可以建议做两件事:

使用 NOT EXISTS而不是 LEFT JOIN + IS NULL .当您告诉引擎您想要在特定集合 Vs 中没有任何出现的记录时，执行计划很可能会有所不同。加入并确保加入的列为空。

SELECT e.Id, e.name, _c.id as componentId, r.Id as reportId
FROM Employees e
INNER JOIN EmployeeReports r on e.Id = r.employeeId and r.reportDate = @reportDate 
CROSS JOIN @componentIds _c
WHERE
    NOT EXISTS (SELECT 'no record' FROM cteCounts AS cn
                WHERE _c.Id = cn.componentId AND r.Id = cn.reportId)

使用临时表而不是 CTE 和/或变量表。如果您必须处理许多行，变量表实际上没有统计信息，一些复杂的 CTE 实际上可能会制定糟糕的执行计划。尝试使用临时表而不是这两个表，看看性能是否有所提升。如果您的行数很高，还可以尝试在它们上创建相关索引。