主页 / dba / 问题 / 118898
Accepted
Greg Bala
Asked: 2015-10-23 05:57:11 +0800 CST

简单但有问题的更新查询

  • 772

我有一个相当简单的更新/查询，多年来一直让我很伤心。

最简单的形式是：

update VillageSemaphore
set TimeStamp = getdate() 
        where VillageID in (@X, @Y)

但是，在某些存储过程中，查询还包括此“OR VillageID in (...)”子查询

update VillageSemaphore
set TimeStamp = getdate() 
        where VillageID in (@X, @Y)

        OR VillageID in  ( -- this subquery can return many rows, many different VillageIDs
        select VSU.SupportingVillageID 
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z       
            and VSU.UnitCount <> 0
            )

请注意，此 OR 可以返回多个 villageID，而不仅仅是一个 @Z。此版本的查询有时会运行很长时间。没有索引重建，统计重建有帮助。当删除并重新填充 Villages 表的内容时，它运行缓慢。在这种情况下，行数将只有几百行。我一直不明白为什么会这样，并且一直忍受着它。

但是，最近我正在查看查询计划：

在此处输入图像描述

与实际行数 (2) 相比，估计行数 (4000) 似乎很大。

我创建了这个统计数据，但它没有帮助

CREATE STATISTICS [stat_x] ON [VillageSU]([UnitCount], [VillageID])

所以我的问题：任何建议为什么会这样以及我可以做些什么来改进它？

作为参考，该表如下所示：

CREATE TABLE VillageSemaphore(
    VillageID    int         NOT NULL,
    TimeStamp    datetime    NOT NULL,
    CONSTRAINT PK97 PRIMARY KEY CLUSTERED (VillageID)
)

更新：按照 srutzky 的建议尝试这个版本的查询

CREATE TABLE #VillagesToLock (VillageID INT NOT NULL);
insert into #VillagesToLock values (@X)
insert into #VillagesToLock values (@Y)
insert into #VillagesToLock select VSU.SupportingVillageID 
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z       
            and VSU.UnitCount <> 0

update VillageSemaphore set TimeStamp = getdate() 
    where VillageID in (select VillageID from #VillagesToLock)

这是目前的结果：http ://screencast.com/t/96KafTPoNGM - 查询计划确实看起来更好。

查询成本也从 3% 下降到 1%，这看起来不错。3% 可能看起来不多，但这是一个 2500 行的存储过程！

问题：我无法将#VillagesToLock.VillageID 设为 PK，因为它不是唯一的。我希望 #VillagesToLock 通常不超过 2-10 行。VillageSemaphore 可能有数千行。在这种情况下是否值得在#VillagesToLock 上建立索引？

11 月 24 日更新 我已经实施了这个替代方案 在此处输入图像描述

查询计划确实看起来好多了 在此处输入图像描述

感谢所有花时间帮助我的人！

sql-server sql-server-2012

3 个回答

  1. 虽然我不相信这是查询本身的问题（当它运行缓慢时你是否检查过阻塞？你是否检查过它运行时发生的等待类型），IN并且OR可能是一个有问题的模式来优化. 您是否考虑过将其分解为多个语句？

    UPDATE dbo.VillageSemaphoreset 
  SET [TimeStamp] = GETDATE() -- TimeStamp is a terrible column name btw 
  WHERE VillageID = @X;

UPDATE dbo.VillageSemaphoreset 
  SET [TimeStamp] = GETDATE()
  WHERE VillageID = @Y;

IF (whatever condition leads you to "sometimes add this OR")
BEGIN
  UPDATE v 
    SET [TimeStamp] = GETDATE()
    FROM dbo.VillageSemaphoreset AS v
    WHERE VillageID = @Z
    AND EXISTS 
    (
      SELECT 1 FROM dbo.VillageSU AS vs
      WHERE vs.VillageID = v.VillageID
    );
END

    这可能会解决估计问题，但我同意 Max 的观点，前导列为 的统计数据UnitCount无论如何都无助于这些查询的估计。

    • 6
  2. Best Answer

    或者，您也可以只创建一个本地临时表，这样 UPDATE 将使用INNER JOIN：

    CREATE TABLE #VillageIDsToUpdate (VillageID INT NOT NULL PRIMARY KEY);

INSERT INTO #VillageIDsToUpdate (VillageID) VALUES (@X);
INSERT INTO #VillageIDsToUpdate (VillageID) VALUES (@Y);
IF (@Z IS NOT NULL)
BEGIN
  INSERT INTO #VillageIDsToUpdate (VillageID)
    SELECT SUVillageID
    FROM   VillageSU
    WHERE  VillageID = @Z;
END;

UPDATE vs
SET    vs.TimeStamp = GETDATE()
FROM   VillageSemaphore vs
INNER JOIN #VillageIDsToUpdate ids
        ON ids.VillageID = vs.VillageID;

    更新：

    我只是想到了一些可能有助于提高重复项过滤效率的方法：如何使用IGNORE_DUP_KEYPK 上的设置？例如：

    CREATE TABLE #VillageIDsToUpdate (VillageID INT NOT NULL PRIMARY KEY
                                            WITH (IGNORE_DUP_KEY = ON));

    如果你这样做，那么下面的工作就如你所愿：

    INSERT INTO #VillageIDsToUpdate (VillageID) VALUES (1);
INSERT INTO #VillageIDsToUpdate (VillageID)
  SELECT tmp.val
  FROM   (VALUES (1), (2), (3), (3)) tmp(val);

SELECT * FROM #VillageIDsToUpdate;

    退货：

    VillageID
---------
1
2
3

    这意味着您可以INSERT按照我上面的建议执行语句，而无需添加DISTINCT或执行任何辅助查询来删除重复项:-)。

    • 3

  3. 您在 VillageSU 中有关于 SUVillageID 的键/索引吗？如果没有，您需要添加它。另外，你有没有试过这个：

    with ctesuv as
    (select SUVillageID as VillageID
        from VillageSU 
        where VillageID = @Z -- if @Z itself is a list of values, you want an `IN` here
    ),
update VillageSemiphore
    set [TimeStamp] = getdate() 
        where VillageID in (@X, @Y, (select * from ctesuv))

    注意：TimeStamp 是 Access 中的保留关键字，也可能在 SQL Server 中。

    或者

    update VillageSemiphore
SET [TimeStamp] = getdate()
   WHERE VillageID in (@X,@Y) OR 
        EXISTS (SELECT 1 FROM (
            select SUVillageID from VillageSU 
            where VillageID = @Z)
        )

    怎么样

    update VillageSemiphore as VS
SET VS.[TimeStamp] = getdate()
    WHERE EXISTS (SELECT 1 FROM (
        SELECT TOP 1
            VS.VillageID, SU.SUVillageID
        FROM VillageSU as SU
        WHERE
            (SU.VillageID = @Z AND SU.SUVillageID = VS.VillageID)
            OR (VS.VillageID IN (@X,@Y)) -- this would cross-join to all rows in SU...the top1 may limit that, but you may need some alternate logic here. I'll have to think a bit more about this
    )
    • 1

相关问题