表值函数扼杀了我的查询性能 [英] Table Valued Function Killing My Query Performance

问题描述

我今天过得很糟糕，试图让查询以我期望的方式执行.我不得不对昨天存在于查询中的表值函数进行轻微更改，该更改对查询产生了巨大的性能影响.在评估执行计划并查看 IO 和时间统计信息后，我发现因为我将函数更改为返回一个表变量而不仅仅是一个结果集，所以它正在对被查询的表之一进行全面扫描.

I was having a horrible time today trying to get a query to perform the way I would expect. I had to make a slight change to a table valued function that lives in the query yesterday and that change created a huge performance impact on the query. After evaluating the execution plan and looking at statistics IO and Time I found that because I changed the function to return a table variable instead of just a result set it was doing a full scan on one of the tables being queried.

我的问题是为什么让它返回表 (TableVariable) 而不仅仅是一个选择/结果集会导致计划发生如此大的变化?

My question is why would having it return the table (TableVariable) instead of just a Select / Result set cause such a big change to the plan?

难倒....

推荐答案

返回表变量将使其成为多语句表值函数，并且由于它是像表一样对待，除非 SQL Server 没有可用的统计信息来制定良好的执行计划 - 因此它将估计函数返回非常少量的行.如果它返回更多的行，那么生成的计划可能远非最佳.

Returning a Table Variable will make it a multi-statement table valued function and can be bad for performance due to the fact that it's treated like a table except there are no statistics available for SQL Server to base a good execution plan on - so it will estimate the function as returning a very small number of rows. If it returns a larger number of rows, then therefore the plan generated could be a lot less than optimal.

然而，仅返回一个 SELECT 使其成为内联表值函数 - 将其更多地视为一个视图.在这种情况下，实际的底层表被带入主查询，并且可以基于适当的统计生成更好的执行计划.您会注意到，在这种情况下，执行计划根本不会提及该函数，因为它基本上只是将函数合并到主查询中.

Whereas, returning just a SELECT makes it an inline table valued function - think of it more as a view. In this case, the actual underlying tables get brought into the main query and a better execution plan can be generated based on proper statistics. You'll notice that in this case, the execution plan will NOT have a mention of the function at all as it's basically just merged the function into the main query.

MSDN 由 CSS SQL Server 工程师提供，包括(引用):

There's a great reference on it on MSDN by CSS SQL Server Engineers including (quote):

但是如果您使用多语句 TVF，它被当作另一个桌子.因为没有可用的统计信息，SQL Server 有做出一些假设并在一般提供低估值.如果你的TVF 只返回几行，它会没事的.但如果你打算用数千个填充 TVF行，如果此 TVF 与其他表，低效的计划可以低基数估计的结果.

But if you use multi-statement TVF, it’s treated as just like another table. Because there is no statistics available, SQL Server has to make some assumptions and in general provide low estimate. If your TVF returns only a few rows, it will be fine. But if you intend to populate the TVF with thousands of rows and if this TVF is joined with other tables, inefficient plan can result from low cardinality estimate.

这篇关于表值函数扼杀了我的查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！