x

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?
more ▼

asked Sep 03, 2010 at 02:00 AM in Default

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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).

more ▼

answered Sep 03, 2010 at 02:12 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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!
Sep 03, 2010 at 02:25 AM WilliamD
@WilliamD - No worries, I wouldn't say it if I didn't mean it.
Sep 03, 2010 at 04:54 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Sep 03, 2010 at 02:41 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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.
Sep 03, 2010 at 02:48 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x251
x108
x11
x2

asked: Sep 03, 2010 at 02:00 AM

Seen: 1320 times

Last Updated: Sep 03, 2010 at 02:00 AM