创建一个创建用户定义函数的存储过程是一个好习惯吗...？
我正在分析一位开发人员同事的 SQL 代码，我可能是完全错误的，但他们所做的事情对我来说似乎并不好。
该设计适用于动态表达式生成器。表达式使用几个表来存储并且系统可以工作。我关心的系统方面是，当存储表达式记录时，会调用一个存储过程，然后创建一个 UDF，将整个表达式展开为一个函数。
这意味着，对于创建的每个唯一表达式，都会有一个可以运行的唯一 UDF 来执行该表达式。我假设这样做是为了可以缓存执行计划并提高性能，而不是每次都构建动态 sql 并运行它。
你们觉得怎么样，这看起来是一个可以接受的解决方案吗？您需要更多信息吗？
I'm analyzing a fellow developer's SQL code and I could be totally wrong, but something they've done doesn't seem good to me.
The design is for a dynamic expression builder. The expressions are stored using a few tables and the system works. The aspect of the system that concerns me is that when the expression records are stored, a stored procedure is called that then creates a UDF that unrolls the entire expression as one function.
So what that means is, for each unique expression created, there will be a unique UDF that can be ran that will execute that expression. I'm assuming that this is being done so that the execution plan can be cached and performance will be increased, as opposed to building dynamic sql and running that each time.
What do you guys think, does it seem like an acceptable solution? Do you need more info?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这对我来说听起来像是一场灾难。主要原因是，如果有人返回并清理了 UDF（除非它们只使用一次，那么您的“执行计划”想法就会失败），软件就会失败。而且，如果你不清理，你最终会得到很多未使用的杂物，尤其是在很长一段时间之后。
进一步阅读时，听起来 UDF 以某种方式存储为元数据，这听起来好一点，但一旦考虑到创建多个 UDF 所产生的所有额外混乱，我不确定您是否会节省那么多。性能是一个问题吗？
This sounds like a recipe for disaster to me. The main reason would be if someone went back and cleaned up the UDFs (unless they are only used once, then your "execution plan" idea is shot) the software fails. And, if you don't clean up, you end up with a lot of clutter not used, especially after a lengthy period of time.
At further reading, it sounds like the UDFs are somehow stored as metadata, which sounds a bit better, but I am not sure you are saving that much once you consider all the extra clutter creating multiple UDFs creates. Is performance an issue?