asked
Data Access Layer - Design advice on replacing UDFs
I am currently trying to get my database devs to embrace set-based programming and am assisting in the redesign of our data access layer. In particular, I want to remove our dependance on UDFs with regard to their RBAR-ness. I am not too sure what the best practice is in this respect. Quite a few of the UDFs are there to "translate" an Id to a human-readable value, or to build a string depending upon certain criteria. Am I right in thinking I should move the queries from the UDFs into a view that has this the same logic but expressed in a set-based manner? I have tested my assumptions thus far replacing the UDFs with a CTE that does the same work and seen huge performance boosts. I know I will have to bring clear arguments with this, as the devs have a poor opinion of views, especially when joining them with other large data-sets (these opinions are not necessarily correct). How do you accomplish this sort of thing in your environments?
I think your approach is certainly valid. The other way to approach it, which might get more traction is to replace the view idea with an inline table function and cross apply. Thereby keeping the functional approach, but also getting out the RBAR factor. I mean, let's face it, what you write is smart and well considered. I would trust you to come and give me advice on my databases (I always come from the approach that another pair of eyes is worth a huge amount). So I think your problem isn't one of knowing what to do, but of persuading the dev's that you're dealing with of that fact. I usually find that the way to win over a developer is to show them a solution that urinates on the performance of the existing solution from a great height. I think you'll probably have to dig into why they think views are bad - for example, were they once described by someone as a 'magic bullet' and then over-used without realising that the query optimizer would still need well optimised indexes on the underlying tables? (I've seen that a few times).
The opinion on views is based upon what you wrote: poor implementation, over-usage etc. Inline functions with cross apply is certainly a valid point, something I hadn't really thought of (although have done so on past projects with great success). I think I'll have to talk with the chief DB architect and puzzle over the implemenation of this. I would like to make this into some sort of standard design practice, so the devs get into automatically thinking of set-based solutions instead of procedural programming. P.S. Thanks for the vote of confidence, means a lot coming from someone like yourself!
@WilliamD - No worries, I wouldn't say it if I didn't mean it.
I would have gone for the table function as my first alternative to build a comparison, over the CTE. I would guess that as it sounds like a big rebuild project that you may find instances where one is better than the other and you will end up with a mix of CTEs and TVFs. It may help your 'sell' to the devs that you have bth of these as alternatives and **they** can discover which is the better option while they develop. Get them to see the speed that set based solution can apply and select the better of the two.
I only played with a CTE so as to have the functionality in an easily reusable manner whilst playing aroung in SSMS. I find it easier to do it this way so that I am not creating objects that I have to clean up later. I would not keep the CTE for permanent use, but encapsulate the logic itself in a view or TVF and add this to the official DAL. Luckily the devs do listen to their DBA (me!) when told that something can/should/must be done better. I will finish up my initial refactoring and display my results to the team and explain what is going on behind the scenes. Thanks for the input.
