在这种情况下创建 CTE 有帮助吗？

Question

我有一个在 SQL Server 2008 中写得很差的查询。

UPDATE  PatientChartImages
SET     PatientChartImages.IsLockDown = @IsLockdown
WHERE   PatientChartImages.IsLockDown = @IsNotLockdown
    AND PatientChartId IN (
    SELECT  PatientCharts.PatientChartId
    FROM    PatientCharts
    WHERE   ( PatientCharts.ChartStatusID = @ChartCompletedStatusID
              OR PatientCharts.ChartStatusID = @ChartOnBaseStatusID
            )
            AND PatientCharts.IsLockDown = @IsNotLockdown
            AND PatientCharts.CompletedOn IS NOT NULL
            AND DATEDIFF(MINUTE, PatientCharts.CompletedOn, GETUTCDATE()) >= ( SELECT
                                                          tf.LockUpInterval
                                                          FROM
                                                          @tblFacCOnf tf
                                                          WHERE
                                                          tf.facilityId = PatientCharts.FacilityId
                                                          ) )

该查询锁定主表并导致超时。如果我首先创建 CTE 所有可更新记录，然后通过加入 CTE 来更新主表。有帮助吗？？

Answer 1

我建议您做的第一件事就是用 EXISTS 替换 IN 条件。其次是将所有这些条件逻辑转移到 CTE 中。第三种方法是将子选择替换为 @tblFacCOnf 和 join。

最后的建议取决于您的业务逻辑，在我看来并不那么重要

所以最后您会得到如下内容

WITH search_cte as (
  SELECT  PatientCharts.PatientChartId
  FROM    PatientCharts 
  JOIN @tblFacCOnf tf on tf.facilityId = PatientCharts.FacilityId
  WHERE   ( PatientCharts.ChartStatusID = @ChartCompletedStatusID
          OR PatientCharts.ChartStatusID = @ChartOnBaseStatusID
        )
        AND PatientCharts.IsLockDown = @IsNotLockdown
        AND PatientCharts.CompletedOn IS NOT NULL
        AND DATEDIFF(MINUTE, PatientCharts.CompletedOn, GETUTCDATE()) >= tf.LockUpInterval
) --cte end                                               
UPDATE  PatientChartImages
SET     PatientChartImages.IsLockDown = @IsLockdown
WHERE   PatientChartImages.IsLockDown = @IsNotLockdown
AND EXISTS (select 1 from PatientChartImages where PatientChartImages.PatientChartId = search_cte.PatientChartId)

Answer 2

如果其他建议不能让您获得足够的速度，我可能建议的另一件事是不要使用表变量。对于大型数据集，临时表通常速度更快，并且可以在需要时建立索引。

Answer 3

更新锁在计算 CTE 所需的时间和更新时间期间保持。 CTE 时间可能导致超时。

将更新目标表所需的锁定时间减少到最短。我建议您创建一个包含两列的临时表。 Col1 是目标表的主键或簇键，Col2 是目标表中所需的值。包装临时表创建并根据事务中的业务逻辑在表中填充值。使用临时表的联接以及单独事务中临时表中的值来更新目标表。更新后删除临时表。

Answer 4

我认为您应该创建一个 SQL 脚本（或存储过程，如果您要从更高级别使用它），将选择的结果存储到游标中（您只需找到要更新的行的 PatientCartId ）然后您应该在更新中使用它，所以答案是肯定的。

测试这一点很容易，您应该将这些命令放入事务中，回滚事务，并且在回滚之前您应该执行选择来测试结果。祝你好运。