1

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?

Mark
  • 595
  • 5
  • 13
  • Definitely needs more info. If this were me I'd probably be looking into a CLR assembly, but maybe that wasn't an option for the team who created your solution. Which version of SQL Server? In what manner is the solution used in other applications? – Yuck Aug 16 '11 at 19:26
  • SQL Server 2008 R2. CLR is probably not a viable option due to the fact that no one will be able to maintain the code after this consultant leaves. I'm seeing CLR as a hard sell at this point, but agree that it would be a good alternative. There are no plans to use the solution in other applications at this time. – Mark Aug 16 '11 at 23:58

1 Answers1

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.

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?

Gregory A Beamer
  • 16,870
  • 3
  • 25
  • 32
  • No performance is not really the issue. I just had a long discussion with the developer about his decision to use UDFs and it ends up that is kind of a crutch requested by the report writers. I can't say I understand the reasoning 100%, but you know how requirements gathering goes. We sometimes suggest solutions that stick and then can't be unstuck after realizing it wasn't the best way. Just another reason to try and keep "how" out of requirements as much as possible. – Mark Aug 17 '11 at 01:19