具有估计行数的表值参数 1 [英] Table Valued Parameters with Estimated Number of Rows 1

问题描述

我在互联网上搜索了几个小时，试图找出如何使用表值参数 (TVP) 提高查询性能.

I have been searching the internet for hours trying to figure out how to improve the performance of my query using table-valued parameters (TVP).

经过数小时的搜索，我终于确定了我认为是问题的根源.在检查我的查询的估计执行计划后，我发现每次使用 TVP 时，我的查询的估计行数都是 1.如果我将 TVP 交换为选择我感兴趣的数据的查询，那么估计的行数会准确得多，约为 7400.这会显着提高性能.

After hours of searching, I finally determined what I believe is the root of the problem. Upon examining the Estimated Execution plan of my query, I discovered that the estimated number of rows for my query is 1 anytime I use a TVP. If I exchange the TVP for a query that selects the data I am interested in, then the estimated number of rows is much more accurate at around 7400. This significantly increases the performance.

然而，在实际场景中，我不能使用查询，我必须使用 TVP.有没有什么方法可以让 SQL Server 在使用 TVP 时更准确地预测行数，以便使用更合适的计划?

However, in the real scenario, I cannot use a query, I must use a TVP. Is there any way to have SQL Server more accurately predict the number of rows when using a TVP so that a more appropriate plan will be used?

推荐答案

TVP 是不维护统计信息的表变量，因此报告只有 1 行.有两种方法可以改进 TVP 的统计数据:

TVPs are Table Variables which don't maintain statistics and hence report only have 1 row. There are two ways to improve statistics on TVPs:

1. 如果您不需要修改 TVP 中的任何值或向其中添加列来跟踪操作数据，那么您可以执行一个简单的语句级 OPTION (RECOMPILE) 在任何使用表变量(TVP 或本地创建)并且使用该表变量做的比简单的 SELECT(即执行 INSERT INTO RealTable (columns) SELECT (columns) FROM @TVP; 不需要语句级重新编译).在 SSMS 中执行以下测试以查看此行为的实际效果:

1. If you have no need to modify any of the values in the TVP or add columns to it to track operational data, then you can do a simple, statement-level OPTION (RECOMPILE) on any query that uses a table variable (TVP or locally created) and is doing more with that table variable than a simple SELECT (i.e. doing INSERT INTO RealTable (columns) SELECT (columns) FROM @TVP; does not need the statement-level recompile). Do the following test in SSMS to see this behavior in action:

DECLARE @TableVariable TABLE (Col1 INT NOT NULL);

INSERT INTO @TableVariable (Col1)
  SELECT so.[object_id]
  FROM   [master].[sys].[objects] so;

-- Control-M to turn on "Include Actual Execution Plan"

SELECT * FROM @TableVariable; -- Estimated Number of Rows = 1 (incorrect)

SELECT * FROM @TableVariable
OPTION (RECOMPILE); -- Estimated Number of Rows = 91 (correct)

SELECT * FROM @TableVariable; -- Estimated Number of Rows = 1 (back to incorrect)

创建一个本地临时表(单个 #)并将 TVP 数据复制到该表中.虽然这确实复制了 tempdb 中的数据，但好处是:

Create a local temporary table (single #) and copy the TVP data to that. While this does duplicate the data in tempdb, the benefits are:

• 与表变量相比，临时表的统计数据更好(即无需语句级重新编译)
• 能够添加列
• 修改值的能力

这篇关于具有估计行数的表值参数 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！