sql-server - 使用 CLR 集成表函数进行高效交叉应用

Question

在 SQL Server 中，我有一个基于 CLR 集成的表值函数 GetArchiveImages。我这样调用它:

SELECT ...
FROM Items
CROSS APPLY GetArchiveImages(Items.ID) AS archiveimages
WHERE ...

问题是每次单独调用该函数都会产生开销。

如果它可以一次与整个表连接，开销会非常小，但由于它为每一行调用一次，因此开销会随着行数的增加而增加。

我不使用存储过程，因为存储过程返回的表不能与任何东西连接(据我所知)。

是否有一种有效的方法来批量连接表和存储过程或函数的结果，而不是逐行连接？

Answer 1

由于 GetArchiveImages 的结果取决于 Items.ID SQL Server 必须为每个项目调用该函数，否则您将无法获得正确的结果。

SQL Server 唯一可以“分解”的函数是 T-SQL 内联表值函数。因此，如果您可以将 CLR 重写为 ITVF，您将获得更好的性能。

根据我的经验，调用 CLR 函数的开销并不大。您更有可能在查询的其他地方遇到问题。例如，SQL Server 不知道该函数将返回多少行，只是假设它是一个(对于每次调用)。这可能会导致在优化过程中其他地方做出错误的决定。

---

更新:

SQL Server 不允许在 CLR 类中保留静态非常量数据。有一些方法可以欺骗系统，例如通过创建静态最终集合对象(您可以从静态集合中添加和删除项目)，但是，出于稳定性原因，我建议不要这样做。

在您的情况下，创建一个使用某种(数据库或文件系统)触发器或按计划自动刷新的缓存表可能是有意义的。无需调用该函数，您只需加入该表即可。