这个跟我测试的数据有所出入(可能跟数据库版本、机器配置有一点关系)。但是大体方向是一致的。Avg CPU Time和Avg Elapsed Time 执行时间依然Function, no distinct subquery > Function, subquery = Inline calculation, subquery > Inline calculation, no distinct subquery
那么接下来,我们先进一个表值函数Triple_tbl,对比表值函数和标量函数的性能。如下所示
CREATE FUNCTION dbo.Triple_tbl (@DataVal INT)RETURNS TABLE ASRETURNSELECT @DataVal * 3 TripleGO DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SET STATISTICS TIME ON; SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple FROM (SELECT DISTINCT DataVal FROM dbo.LargeTable) AS d; SELECT MAX(3 * DataVal) AS MaxTriple FROM dbo.LargeTable AS d; SELECT MAX(t.Triple) AS MaxTripleFROM dbo.LargeTable lCROSS APPLY dbo.Triple_tbl(l.DataVal) t SET STATISTICS TIME OFF;GO
从下可以看出,表值函数比标量函数性能要好很多,所以用表值函数替换标量函数也是一个可以考虑的优化方案。
参考资料:
https://www.captechconsulting.com/blogs/performance-considerations-of-user-defined-functions-in-sql-server-2012
https://connect.microsoft.com/SQLServer/feedback/details/524983/user-defined-function-performance-is-unacceptable