SQL 存储过程中 While 循环的速度问题

Question

我正在接管一些试图一次存储ID列表的代码，我发现这段代码对于我们尝试完成的操作运行得很慢。另外，在某些情况下，由于大量 id，会导致死锁。

USE [store]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateImagePriority]
   @separator CHAR(1),
   @filename  varchar(50),
   @parentId   int,
   @slaveIds varchar(8000)
   AS
   BEGIN      
      SET NOCOUNT ON
      DECLARE @SLAPriorityint
      DECLARE @separator_position INT 
      DECLARE @array_value VARCHAR(50)
      SET @slaveIds = @slaveIds + @separator
      SET @SLAPriority= 0
      WHILE PATINDEX('%' + @separator + '%', @slaveIds ) <> 0
           BEGIN
               SET @SLAPriority= @SLAPriority+ 1
               SELECT  @separator_position = PATINDEX('%' + @separator + '%',@slaveIds )
               SELECT  @array_value = LEFT(@slaveIds , @separator_position - 1)
               SELECT  Array_Value = @array_value
               SELECT  @slaveIds = STUFF(@slaveIds , 1, @separator_position, '')
               UPDATE image_info
               SET SLA_PRIORITY = @SLAPriority
               WHERE FILE=@filename and EXT_PAR_ID=@parentId   and SLA_ID=@array_value
           END
      SET NOCOUNT OFF
   END

这是我们将传入的示例：

例如

separator = ','
filename = 'burgerking'
parentId = '1859'
slaveIds = '15,16,19,20,21,25,28,29,30,38,99'

有关如何提高此代码速度的任何建议。

提前感谢！

Answer 1

您正在寻找的是一个表值函数，用于将值拆分为表。然后，您只需要一个UPDATE .. FROM .. JOIN语句即可。

CREATE PROCEDURE [dbo].[UpdateImagePriority]
   @separator CHAR(1),
   @filename  varchar(50),
   @parentId   int,
   @slaveIds varchar(8000)
AS
set @slaveIds = @slaveIds + @separator
;WITH SplitString AS
(
    SELECT
        1 ID,LEFT(@slaveIds,CHARINDEX(',',@slaveIds)-1) AS Part,RIGHT(@slaveIds,LEN(@slaveIds)-CHARINDEX(',',@slaveIds)) AS Remainder
    UNION ALL
    SELECT
        ID+1,LEFT(Remainder,CHARINDEX(',',Remainder)-1),RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
        FROM SplitString
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
)
update i
SET SLA_PRIORITY = s.ID
from splitstring s
join image_info i on i.[FILE]=@filename and i.EXT_PAR_ID=@parentId and i.SLA_ID= s.Part
where s.Part > ''

对于SQL Server 2000，或者只是为了使字符串拆分可重用，我从另一个问题中提升了这个函数。

create function dbo.SplitString 
    (
        @str varchar(8000), 
        @separator char(1)
    )
    returns table
    AS
    return (
        with tokens(p, a, b) AS (
            select 
                1, 
                1, 
                charindex(@separator, @str)
            union all
            select
                p + 1, 
                b + 1, 
                charindex(@separator, @str, b + 1)
            from tokens
            where b > 0
        )
        select
            p Id,
            substring(
                @str, 
                a, 
                case when b > 0 then b-a ELSE 8000 end) 
            AS Part
        from tokens
      )
GO

然后你的 SP 就变成了

CREATE PROCEDURE [dbo].[UpdateImagePriority]
   @separator CHAR(1),
   @filename  varchar(50),
   @parentId   int,
   @slaveIds varchar(8000)
AS
update i
SET SLA_PRIORITY = s.ID
from dbo.splitstring(@slaveIds,@separator) s
join image_info i on i.[FILE]=@filename and i.EXT_PAR_ID=@parentId and i.SLA_ID= s.Part
where s.Part > ''
GO

Answer 2

使用函数将数组转换为 XML 文档，然后可以轻松地将其作为整数的单列表返回：

use tempdb
go
create function dbo.ParseIDs(@ids varchar(max), @separator char(1)) returns @rtn table (
  id int
)
as
begin
  declare @xml xml
  set @xml = '<root><n>' + replace(@ids, @separator, '</n><n>') + '</n></root>'
  insert into @rtn
  select id.value('.', 'int')as id
  from @xml.nodes('/root/n') as records(id)
  return
end
go
declare @buf varchar(max) = '15,16,19,20,21,25,28,29,30,38,99'
select * from dbo.ParseIDs(@buf, ',')
go
drop function dbo.ParseIDs
go

这将返回以下内容：

id
----
15
16
19
20
21
25
28
29
30
38
99

然后很容易做这样的事情：

UPDATE image_info
SET SLA_PRIORITY = @SLAPriority
WHERE FILE=@filename and EXT_PAR_ID=@parentId   and SLA_ID in (
  select id from dbo.ParseIDs(@array_value, ',')
)

这甚至可能更好：

UPDATE tbl
SET    tbl.SLA_PRIORITY = @SLAPriority
FROM   dbo.ParseIDs(@array_value, ',') as x
       inner join image_info tbl on x.id = tbl.SLA_ID
WHERE  tbl.FILE=@filename and tbl.EXT_PAR_ID=@parentId

Answer 3

执行基于集合的更新：

    USE [store]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[UpdateImagePriority]
   @separator CHAR(1),
   @filename  varchar(50),
   @parentId   int,
   @slaveIds varchar(8000)
   AS
   BEGIN      
      SET NOCOUNT ON
      DECLARE @SLAPriorityint
      DECLARE @separator_position INT 
      DECLARE @array_value VARCHAR(50)
      SET @slaveIds = @slaveIds + @separator
      SET @SLAPriority= 0
      DECLARE @slaveIdTable Table
      (
        ids INT,
        SlaPriority INT
      )
      WHILE PATINDEX('%' + @separator + '%', @slaveIds ) <> 0
           BEGIN
               SET @SLAPriority= @SLAPriority+ 1
               SELECT  @separator_position = PATINDEX('%' + @separator + '%',@slaveIds )
               SELECT  @array_value = LEFT(@slaveIds , @separator_position - 1)
               -- get table of ids
               INSERT INTO @slaveIdTable (ids,SlaPriority) VALUES(@array_value,@SLAPriority);
               SELECT  @slaveIds = STUFF(@slaveIds , 1, @separator_position, '')
           END
    UPDATE ii
        SLA_PRIORITY = @SLAPriority
    FROM image_info ii
    JOIN @slaveIdTable st ON st.ids = st.SLA_ID
    WHERE
        st.[FILE] =  @filename AND
        st.EXT_PAR_ID = @parentId                   
      SET NOCOUNT OFF
   END

理查德的回答更优雅一些。 当我在做我的时没有看到它。