主页 / dba / 问题 / 283805
Accepted
Newbie-DBA
Asked: 2021-01-23 12:51:51 +0800 CST

帮助调整此更新查询或可能的重写

  • 772

我有以下 SQL 查询，它在 avg 上运行。40 秒，需要一些指导，我可以在哪里改进或重写：

   Declare @status_D INT,
      Declare @StatusIP INT,
      Declare @Status_F INT,
    
    SELECT @status_D = Id From Batchtablestatus (NOLOCK) Where desc ='Value1';
    SELECT @StatusIP = Id From Batchtablestatus (NOLOCK) Where desc ='Value2';
    SELECT @Status_F = Id From Batchtablestatus (NOLOCK) Where desc ='Value3'

BEGIN TRAN

 UPdate U
        SET 
         U.[id]= @StatusIP,
         U.[EnabledDate]= Getdate()
         OUTPUT deleted.col1,
         deleted.col2,
         deleted.col3,
         deleted.col4,
         Inserted.id

FROM (

SELECT TOP 5000 * FROM BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime] )U;

  COMMIT TRAN

我收到来自 blitzcache 的警告，因为强制序列化、昂贵的排序、不可分割和合并加入 "Mant to Many" = True

我们唯一的索引是在 id 上，它是一个以 id 作为前导键列的 NC 索引。

如果可以的话，我会尝试更新我的问题，如果执行计划。截至目前，SELECT 中的表约为 1 亿。在 col1 上也有 PK/CI。

我已经问过用户为什么我们在那个拉近 50 多列但在更新中使用较少的 TOP 中做 *，可能 bi 想错了，但我想到了。

将索引添加到修改时间似乎也无济于事。

请建议还有什么可以检查的。谢谢

计划-索引搜索发生在 NC 索引 id 上 在此处输入图像描述

sql-server query-performance

2 个回答

  1. Best Answer

    警告

    • 强制序列化是因为OUTPUT（没有目标或@table变量会导致这种情况，但不是#temp表）
    • 昂贵的排序是因为您在ModifiedTime没有支持索引和查询模式的情况下进行排序
    • 非 SARGable 警告是因为（如果我记得我是如何正确编写检查的）局部变量。

    如何修复它

    您需要一个良好的支持索引：

    CREATE INDEX super_awesome
ON dbo.BatchChangeTable
    (id, ModifiedTime)
INCLUDE
    (EnabledDate);

    但是查询的编写方式IN将被解释为范围谓词，并且ORDER BY ModifiedDate可能仍会导致排序操作。为了充分利用索引，您需要更改表单以使用两个相等谓词和 a 。UNION ALL

    您还需要重写查询以使用动态 SQL，以免受到局部变量的副作用的影响。

    成品将如下所示：

    BEGIN TRAN;

/*I'm guessing here, address local factors*/
CREATE TABLE #output(col1 int, col2 int, col3 int, col4 int, id int)

/*Use parameterized dynamic SQL*/
DECLARE @sql nvarchar(MAX) = N''

/*Hello I am your friend*/
SET @sql += N'
UPDATE u
SET
    u.id = @StatusIP,
    u.EnabledDate = GETDATE()
OUTPUT
    deleted.col1,
    deleted.col2,
    deleted.col3,
    deleted.col4,
    Inserted.id
INTO #output 
FROM
(
    SELECT TOP (5000)
        b.*
    FROM 
    (
         SELECT 
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @status_D
         
         UNION ALL 
         
         SELECT 
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @Status_F
         ORDER BY b2.ModifiedTime
    ) AS b
    ORDER BY b.ModifiedTime
) AS u;
';

EXEC sp_executesql @sql,
                   N'@status_D int,
                     @StatusIP int,
                     @Status_F int',
                     @status_D,
                     @StatusIP,
                     @Status_F;

SELECT 
    o.*
FROM #output AS o

COMMIT TRAN;

    您需要稍微调整一下以适应您的实际情况，但它应该让您朝着正确的方向前进。

    • 2

  2. 基于可用信息的想法：

    1. 添加非聚集索引（id，ModifiedTime）或（ModifiedTime，id），根据您的信息不确定
    2. 尝试重写更新。

    代码：

    update BatchChangeTable 
        SET 
         U.[id]= @StatusIP,
         U.[EnabledDate]= Getdate()
         OUTPUT deleted.col1,
         deleted.col2,
         deleted.col3,
         deleted.col4,
         Inserted.id
where col1 in (select top 5000 col1 from BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime])
    • 1

相关问题