由于表扫描，表值参数的性能很慢 [英] Table Valued Parameter has slow performance because of table scan

问题描述

我有一个将参数传递给 SQL 过程的应用程序.其中一个参数是表值参数，其中包含要包含在 where 子句中的项目.

I have an aplication that passes parameters to a procedure in SQL. One of the parameters is an table valued parameter containing items to include in a where clause.

因为当我将 TVP 加入一个有 200 万行的表时，表值参数没有附加统计信息，所以查询速度非常慢.

Because the table valued parameter has no statistics attached to it when I join my TVP to a table that has 2 mil rows I get a very slow query.

我有什么选择?

同样，目标是将某些值传递给将包含在 where 子句中的过程:

Again, the goal is to pass certain values to a procedure that will be included in a where clause:

select * from table1 where id in(从@mytvp 中选择 id)

select * from table1 where id in (select id from @mytvp)

或

select * from table1 t1 join @mytpvt1.id 上的 tvp = tvp.id

select * from table1 t1 join @mytpv tvp on t1.id = tvp.id

推荐答案

如前所述 此处 并解释了 此处 您可以对表类型设置主键和唯一约束.例如.

As mentioned here and explained here you can have primary key and unique constraints on the table type. E.g.

CREATE TYPE IdList AS TABLE ( Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY )

但是，检查它是否像现在一样提高了您的性能，这些索引在填充 TVP 时存在，这可能会导致反效果，具体取决于您的输入是否已排序和/或您是否使用不止一列.

However, check if it improves performance in your case as now, these indexes exist when the TVP is populated which might lead to a counter effect depending if your input is sorted and/or if you use more than one column.

这篇关于由于表扫描，表值参数的性能很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！