INSERT INTO降低表值FUNCTION的速度

Question

在SQL Server 2008方面，我有一个表值函数，它接收45k个合并到单个VARBINARY(MAX)中的整数ID，将它们拆分并返回为表。SplitIds耗时5秒。正如我在估计执行计划中所看到的，100%是"表插入"。有可能以某种方式加快这个功能吗？

ALTER FUNCTION [dbo].[SplitIds](@data VARBINARY(MAX))
RETURNS @result TABLE(Id INT NOT NULL)
AS
BEGIN
    IF @data IS NULL
        RETURN
    DECLARE @ptr INT = 0, @size INT = 4
    WHILE @ptr * @size < LEN(@data)
    BEGIN
        INSERT INTO @result(Id)
        VALUES(SUBSTRING(@data, @ptr * @size + 1, @size))
        SET @ptr += 1
    END
    RETURN
END

目前在C#端，它在Linq中被用于SQL查询的下一种方式：

XDbOrder[] orders =
    database.SplitIds(ConvertToVarbinary(orderIds))
    Join(
        database.Get<XDbOrder>,
        r = r.Id,
        o => o.Id,
        (r, o) => o).
    ToArray();

更普遍的问题是：在Linq-to-SQL中，是否有可能在没有SplitIds的情况下实现下一件事？.Contains不起作用——它创建了包含2100多个SQL参数的查询并崩溃。

int[] orderIds = { ... 45k random entries .....};
XDbOrder[] orders =
    database.Get<XDbOrder>().
    Where(o => orderIds.Contains(o.Id)).
    ToArray();

Answer 1

您可以尝试一种更基于集合的方法。

（我保留了多语句TVF方法，因为生成数字表的内联方法在隔离状态下运行良好，但当合并到更大的查询中时，执行计划可能会非常糟糕——这确保了拆分只发生一次）

我还在返回表中添加了一个主键，这样它就包含了一个有用的索引。

CREATE FUNCTION [dbo].[SplitIds](@data VARBINARY(MAX))
RETURNS @result TABLE(Id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY=ON))
AS
  BEGIN
      IF @data IS NULL
        RETURN
      DECLARE @size INT = 4;
      WITH E1(N)
           AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
               SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
               SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), -- 1*10^1 or 10 rows
           E2(N)
           AS (SELECT 1 FROM   E1 a, E1 b), -- 1*10^2 or 100 rows
           E4(N)
           AS (SELECT 1 FROM   E2 a, E2 b), -- 1*10^4 or 10,000 rows
           E8(N)
           AS (SELECT 1 FROM   E4 a, E4 b), -- 1*10^8 or 100,000,000 rows
           Nums(N)
           AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
               FROM   E8)
      INSERT INTO @result
                  (Id)
      SELECT TOP (DATALENGTH(@data)/@size) SUBSTRING(@data, N * @size + 1, @size)
      FROM   Nums
      RETURN
  END 

以下对我来说在大约160ms内完成

DECLARE @data VARBINARY(MAX) = 0x
WHILE DATALENGTH(@data) < 184000
  SET @data = @data + CRYPT_GEN_RANDOM(8000)
SELECT COUNT(*)
FROM   [dbo].[SplitIds](@data) 

Answer 2

这是我的基于集合的方法版本

create FUNCTION [dbo].[SplitIds1](@data VARBINARY(MAX))
returns table with SCHEMABINDING
as
return
WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2), -- 10*100
e4(n) AS (SELECT 1 FROM e3 A CROSS JOIN e3 B), -- 1000*1000
Numbers(ptr,Size) AS (SELECT ROW_NUMBER() OVER (ORDER BY n)-1,4 FROM e4)
SELECT SUBSTRING(@data, ptr * Size + 1, Size) as Id
FROM Numbers
WHERE ptr * Size < LEN(@data)

关于我的方法的一些注意事项

• 在函数中添加SCHEMABINDING将避免execution plan中不必要的Table spool运算符
• 还删除了@size变量，因为它在函数内部是硬编码的
• 将Multi-Statement表值函数更改为Inline表值函数，使您可以像任何view或select查询一样查看函数内select语句的execution plan