sql - 生成唯一名称的性能问题

Question

我在 SQL Server DB 中有一个表“对象”。它包含对象的名称(字符串)。
我有一个需要插入到“对象”表中的新对象的名称列表，在一个单独的表“NewObjects”中。此操作将被称为“导入”。

如果记录名称已经存在于“Objects”中，我需要为要从“NewObjects”导入到“Objects”的每条记录生成一个唯一名称。这个新名称将存储在旧名称的“NewObjects”表中。

DECLARE @NewObjects TABLE
(
    ...
    Name varchar(20),
    newName nvarchar(20)
)

我已经实现了一个存储过程，它为要从“NewObjects”导入的每条记录生成唯一的名称。但是，我对 1000 条记录(在“NewObjects”中)的表现并不满意。
我需要帮助来优化我的代码。下面是实现:

PROCEDURE [dbo].[importWithNewNames] @args varchar(MAX)

-- Sample of @args is like 'A,B,C,D' (a CSV string)
...


DECLARE @NewObjects TABLE
(
    _index int identity PRIMARY KEY,
    Name varchar(20),
    newName nvarchar(20)
)

-- 'SplitString' function: this is a working implementation which is right now not concern of performance
INSERT INTO @NewObjects (Name)
SELECT * from SplitString(@args, ',')

declare @beg int = 1
declare @end int
DECLARE @oldName varchar(10)

-- get the count of the rows
select @end = MAX(_index) from @NewObjects

while @beg <= @end
BEGIN
    select @oldName = Name from @NewObjects where @beg = _index

    Declare @nameExists int = 0

    -- this is our constant. We cannot change
    DECLARE @MAX_NAME_WIDTH int = 5

    DECLARE @counter int = 1
    DECLARE @newName varchar(10)
    DECLARE @z varchar(10)

    select @nameExists = count(name) from Objects where name = @oldName
    ...
    IF @nameExists > 0
    BEGIN
        -- create name based on pattern 'Fxxxxx'. Example: 'F00001', 'F00002'.
        select @newName = 'F' + REPLACE(STR(@counter, @MAX_NAME_WIDTH, 0), ' ', '0')

        while EXISTS (select top 1 1 from Objects where name = @newName)
         OR EXISTS (select top 1 1 from @NewObjects where newName = @newName)
        BEGIN
            select @counter = @counter + 1
            select @newName = 'F' + REPLACE(STR(@counter, @MAX_NAME_WIDTH, 0), ' ', '0')
        END

        select top 1 @z = @newName from Objects

        update @NewObjects
        set newName = @z where @beg = _index
    END

    select @beg = @beg + 1
END

-- finally, show the new names generated
select * from @NewObjects

Answer 1

免责声明:我无法测试这些建议，因此可能存在语法错误，您在实现它们时必须自行解决。他们在这里作为指南来修复此过程，但也帮助您提高 future 项目的技能。

一个刚刚略过的优化，当你迭代更大的集合时会变得更加普遍，这里的代码是:

select @nameExists = count(name) from Objects where name = @oldName
...
IF @nameExists > 0

考虑将其更改为:

IF EXISTS (select name from Objects where name = @oldName)

此外，而不是这样做:

-- create name based on pattern 'Fxxxxx'. Example: 'F00001', 'F00002'.
select @newName = 'F' + REPLACE(STR(@counter, @MAX_NAME_WIDTH, 0), ' ', '0')

while EXISTS (select top 1 1 from Objects where name = @newName)
 OR EXISTS (select top 1 1 from @NewObjects where newName = @newName)
BEGIN
    select @counter = @counter + 1
    select @newName = 'F' + REPLACE(STR(@counter, @MAX_NAME_WIDTH, 0), ' ', '0')
END

考虑一下:

DECLARE @maxName VARCHAR(20)
SET @newName = 'F' + REPLACE(STR(@counter, @MAX_NAME_WIDTH, 0), ' ', '0')

SELECT @maxName = MAX(name) FROM Objects WHERE name > @newName ORDER BY name
IF (@maxName IS NOT NULL)
BEGIN
    @counter = CAST(SUBSTRING(@maxName, 2) AS INT)
    SET @newName = 'F' + REPLACE(STR(@counter, @MAX_NAME_WIDTH, 0), ' ', '0')
END

这将确保您不会为了找到生成名称的最大整数值而进行迭代和多次查询。

此外，根据我所拥有的很少的上下文，您还应该能够再进行一次优化，以确保您只需执行上述一次操作， 曾经 .

DECLARE @maxName VARCHAR(20)
SET @newName = 'F' + REPLACE(STR(@counter, @MAX_NAME_WIDTH, 0), ' ', '0')

IF (@beg = 1)
BEGIN
    SELECT @maxName = MAX(name) FROM Objects WHERE name > @newName ORDER BY name
    IF (@maxName IS NOT NULL)
    BEGIN
        @counter = CAST(SUBSTRING(@maxName, 2) AS INT)
        SET @newName = 'F' + REPLACE(STR(@counter, @MAX_NAME_WIDTH, 0), ' ', '0')
    END
END

我说您可以进行优化的原因是因为除非您不必担心其他实体在此期间插入记录 看起来像你的人 (例如 Fxxxxx)，那么您只需找到 MAX 一次，只需 迭代 @counter在循环中。

事实上，您实际上可以将整个部分拉出循环。你应该能够很容易地推断出来。只需拉动 DECLARE和 SET的 @counter连同 IF (@beg = 1) 中的代码一起输出. 但是一步一步来。

另外，更改此行:

select top 1 @z = @newName from Objects

对此:

SET @z = @newName

因为你是 字面意思 运行查询到 SET两个局部变量。 这可能是导致性能问题的一个重要原因。 除非您实际上是从 SELECT 中设置变量，否则您可以进入的一个好习惯是声明，使用 SET局部变量操作。您的代码中还有其他一些地方适用，请考虑以下行:

select @beg = @beg + 1

改用这个:

SET @beg = @beg + 1

最后，如上所述关于简单 迭代 @counter , 在循环的末尾，你有这一行:

select @beg = @beg + 1

只需添加一行:

SET @counter = @counter + 1

你是金子!

因此，回顾一下，您可以仅收集最大冲突名称 一次所以你将摆脱所有这些迭代。您将开始使用 SET摆脱像 select top 1 @z = @newName from Objects 这样的性能缠身的线条您实际上是在查询表以设置两个局部变量的位置。您将利用 EXISTS方法而不是设置一个利用 AGGREGATE 的变量功能 COUNT做这项工作。

让我知道这些优化是如何工作的。