14 -【cmu15-721】【高级数据库系统】【卡内基梅隆大学】【中英字幕】

1. 本课程将讨论服务器端的用户定义函数（UDF）和用户定义过程（UDS）的逻辑执行。

2. UDFs是由应用程序开发人员编写的函数，用于扩展数据库系统的功能。

3. UDFs可以执行各种计算，包括循环、条件语句和调用其他SQL语句和函数。

4. UDFs可以嵌入在SQL查询中的任何位置，以提供更多的功能和灵活性。

5. 不同的数据库系统支持不同的UDF语言，如SQL PSM、Transact SQL和PL/SQL等。

6. UDFs (User-Defined Functions) can provide modularity and code reuse in database systems.

7. UDFs written in procedural languages like PL/SQL can be difficult for the database system to optimize and parallelize.

8. UDFs can cause performance issues and unpredictable behavior in queries due to their black box nature.

9. The Freud project in SQL Server aims to convert imperative UDFs into relational algebra expressions for better optimization and inlining.

10. Freud allows UDFs to be converted into subqueries, which can be optimized by the query optimizer without requiring changes to the UDF code.

11. UDF（用户定义函数）是类似子查询的概念，除非进行内联或去相关子查询的转换，否则它将被视为函数调用，需要执行嵌套查询并返回结果。

12. 处理嵌套查询的两种方法是去相关或将其重写为连接，或者将嵌套查询提取出来作为子计划运行，并将结果存储在临时表中进行连接。

13. 使用Lateral Join（横向连接）可以让嵌套查询在同一层级中引用其他表或查询的属性，避免了多层嵌套的问题。

14. 使用Lateral Join（横向连接）可以将多个子查询放在FROM子句中，使它们可以相互引用，而不需要嵌套。

15. 将UDF内联到查询中后，查询优化器可以对其进行各种优化，例如动态切片和优化冗余计算，从而提高查询性能。

16. Freud is a technique that optimizes the performance of user-defined functions (UDFs) in SQL queries by inlining them and eliminating unnecessary computations.

17. Freud can significantly improve the performance of UDFs that involve joins or subqueries, but it cannot handle loops, exceptions, or cursors.

18. The Appfel technique is an alternative to Freud that converts UDFs into common table expressions (CTEs) and supports control flow constructs like loops.

19. Freud has been implemented in SQL Server and has shown significant performance gains in real-world workloads.

20. SQL Server's optimizer has limitations in decorrelating subqueries, while the Appfel technique provides a systematic approach to eliminate and decorrelate any subquery.