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.
我正在分析一个开发人员的SQL代码,我可能完全错了,但他们所做的事情对我来说似乎并不好。
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.
该设计适用于动态表达式构建器。使用几个表存储表达式,系统可以正常工作。关注我的系统的一个方面是,当存储表达式记录时,调用存储过程,然后创建一个UDF,将整个表达式展开为一个函数。
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.
所以这意味着,对于每个创建的唯一表达式,都会有一个可以运行的唯一UDF来执行该表达式。我假设这样做是为了可以缓存执行计划并提高性能,而不是每次都构建动态sql并运行它。
What do you guys think, does it seem like an acceptable solution? Do you need more info?
你们有什么想法,它看起来像是一个可以接受的解决方案吗?你需要更多信息吗?
1 个解决方案
#1
3
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.
这对我来说听起来像是一场灾难。主要原因是如果有人回去清理UDF(除非他们只使用一次,然后你的“执行计划”的想法被拍摄)软件失败。而且,如果你不清理,最终会导致很多杂乱,特别是在很长一段时间后。
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?
在进一步阅读时,听起来UDF以某种方式存储为元数据,这听起来好一点,但是一旦你认为创建多个UDF创建的所有额外混乱,我不确定你是否节省了这么多。性能是一个问题吗?
#1
3
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.
这对我来说听起来像是一场灾难。主要原因是如果有人回去清理UDF(除非他们只使用一次,然后你的“执行计划”的想法被拍摄)软件失败。而且,如果你不清理,最终会导致很多杂乱,特别是在很长一段时间后。
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?
在进一步阅读时,听起来UDF以某种方式存储为元数据,这听起来好一点,但是一旦你认为创建多个UDF创建的所有额外混乱,我不确定你是否节省了这么多。性能是一个问题吗?