sql-server - SQL Server 中用户定义表类型的性能

Question

我们一直在使用User-Defined Table Types将整数列表传递给我们的存储过程。

然后，我们使用它们连接到存储过程查询中的其他表。

例如:

CREATE PROCEDURE [dbo].[sp_Name]
(
    @Ids [dbo].[OurTableType] READONLY  
)
AS
    SET Nocount ON

    SELECT
        *
    FROM
        SOMETABLE
        INNER JOIN @Ids [OurTableType] ON [OurTableType].Id = SOMETABLE.Id

当使用较大的数据集时，我们发现性能非常差。

我们用来加快速度的一种方法是将内容转储到临时表中，然后将其连接起来。

例如:

CREATE PROCEDURE [dbo].[sp_Name]
(
    @Ids [dbo].[OurTableType] READONLY  
)
AS
    SET Nocount ON
    CREATE TABLE #TempTable(Id INT)
    INSERT INTO #TempTable
    SELECT Id from @Ids

    SELECT
        *
    FROM
        SOMETABLE
        INNER JOIN #TempTable ON #TempTable.Id = SOMETABLE.Id

    DROP TABLE #TempTable

这确实显着提高了性能，但我想获得一些关于这种方法的意见以及我们尚未考虑的任何其他后果。另外，解释一下为什么这会提高性能也可能很有用。

注意有时我们可能需要传入的不仅仅是一个整数，因此我们不使用逗号分隔的列表或类似的东西。

Answer 1

SQL Server 2019 和 SQL Azure

Microsoft 实现了一项名为 Table Variable Deferred Compilation 的新功能这在很大程度上解决了 SQL Server 早期版本中表变量的性能问题:

With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. This is identical to the behavior of temporary tables, and this change results in the use of actual cardinality instead of the original one-row guess.

此行为可用且开箱即用，无需选择加入。不幸的是，它仍然可能遭受 parameter sniffing issues 的困扰。 ，但总体而言这是一个巨大的改进。

<小时/>

SQL Server 2017 及更早版本

JOIN 性能不佳的主要原因是表值参数 (TVP) 是表变量。表变量不保留统计信息，并且在查询优化器看来只有 1 行。因此，他们可以执行类似 INSERT INTO Table (column_list) SELECT column_list FROM @TVP; 的操作，但不能执行 JOIN 操作。

有一些方法可以尝试解决这个问题:

1. 将所有内容转储到本地临时表(您已经在这样做)。这里的一个技术缺点是，您将在 tempdb 中复制传递到 TVP 的数据(TVP 和临时表都在其中存储数据)。

2. 尝试定义用户定义的表类型以具有聚集主键。您可以在 [Id] 字段上内联执行此操作:

   [ID] INT NOT NULL PRIMARY KEY
   

   不确定这对性能有多大帮助，但值得一试。

3. 将选项(重新编译)添加到查询中。这是一种让查询优化器查看表变量中有多少行的方法，以便可以进行正确的估计。

   SELECT column_list
   FROM   SOMETABLE
   INNER JOIN @Ids [OurTableType]
           ON [OurTableType].Id = SOMETABLE.Id
   OPTION (RECOMPILE);
   

   这里的缺点是您有一个RECOMPILE，每次调用此过程时都会花费额外的时间。但这可能是整体净 yield 。

4. 从 SQL Server 2014 开始，您可以利用内存中 OLTP 并为用户定义的表类型指定 WITH (MEMORY_OPTIMIZED = ON)。请参阅Scenario: Table variable can be MEMORY_OPTIMIZED=ON了解详情。我听说这绝对有帮助。不幸的是，在 SQL Server 2014 和 SQL Server 2016 RTM 中，此功能仅在 64 位企业版中可用。但是，从 SQL Server 2016 SP1 开始，所有版本都可以使用此功能(SQL Server Express LocalDB 可能是异常(exception))。

PS。不要执行 SELECT *。始终指定一个列列表。除非执行类似于 IF EXIST(SELECT * FROM)... 的操作。