当我（手动）将表值函数的结果缓存在临时表中时，为什么查询执行速度会快得多？
为什么查询版本 2 速度如此之快？
我怀疑数据库引擎多次调用表值函数“GetUsageStatistic”，那么有没有办法告诉引擎“GetUsageStatistic”是确定性的并且应该只调用一次？
查询版本 1
--Takes ~10 minutes
select *
from RosterLevel r
left join GetUsageStatistics( @mindate, @maxdate ) usage on r.UserID = usage.UserID;
查询版本 2
--Takes ~10 seconds
select * into #usage from GetUsageStatistics( @mindate, @maxdate );
select *
from RosterLevel r
left join #usage on r.UserID = #usage.UserID;
Why is Query Version 2 so much faster?
I suspect the DB Engine is calling the Table-Valued-Function "GetUsageStatistic" multiple times, so is there a way to tell the engine that "GetUsageStatistic" is deterministic and should be called only once?
Query Version 1
--Takes ~10 minutes
select *
from RosterLevel r
left join GetUsageStatistics( @mindate, @maxdate ) usage on r.UserID = usage.UserID;
Query Version 2
--Takes ~10 seconds
select * into #usage from GetUsageStatistics( @mindate, @maxdate );
select *
from RosterLevel r
left join #usage on r.UserID = #usage.UserID;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
正如评论中提到的，最好的答案是分析吐出的任何执行计划。除此之外，你的直觉可能是对的，但除了 SQL Server 自动尝试的任何缓存之外，我并没有想到你可以提供的查询提示来表明该函数是确定性的，但欢迎你尝试查询提示 MSDN 中提到的一些内容页面。我的第一个测试可能会利用表格提示。
As mentioned in the comments, the best answer is to analyze whatever execution plan is spit out. Barring that, your intuition is probably right, but aside from whatever caching SQL Server automatically attempts, there's not much coming to my head in the way of query hints you can provide to indicate that the function is deterministic, but you're welcome to try a few things out mentioned in the Query Hints MSDN page. My first tests would probably draw on Table Hints.
如果您在第一个示例中使用该函数，它会被调用多次——为 RosterLevel 表中的每条记录调用一次。它每次返回一个（可能）不同的表，具体取决于连接字段。
如果您在第二个示例中使用该函数，则它仅被调用一次。从那里开始，表变量就位于内存中，您不必一遍又一遍地进行读取。
If you use the function in your first example, it is called many times -- once for each record in your RosterLevel table. It returns a (potentially) different table each time, depending on the join field.
If you use the function in your second example, it is only called once. From there, the table variable is in memory, and you're not having to do a read over and over.