CODEWITHSUNDEEP
sqlperformancesql-server-2008coding-stylesql-execution-plan
immutabl
immutabl

Reputation: 6903

User-Defined Functions - Are they poor coding practice?

I'm writing reports with fairly complex datasets, lots of joins. To simplify matters, and because I'm basically an OO developer, I've been writing little (usually scalar) functions to do the work that could be done by joining onto a subquery. This sort of thing:

SELECT 
    x.Name, x.userId, 
    ... [more columns and joins]
    dbo.CountOrders(x.userId)
FROM Customers x 
WHERE ...

Is this good practice? Sloppy? Slow? Should I be writing regular T-SQL to do this stuff?

Upvotes: 5

Views: 460

Answers (3)

Martin Smith
Martin Smith

Reputation: 453727

I would pretty much never have a scalar UDF that does data access.

Scalar UDFs can't get expanded out by the optimiser and need to be evaluated RBAR. It is fairly well established that this is not a good idea.

Some example reading.

Upvotes: 3

Godeke
Godeke

Reputation: 16281

I don't think there is much harm in doing function call computations in SQL as long as it is clear where they are being done to the users of the table. All you have done here is make a shortcut to a subquery. However, I never do this with my primary access queries as people start to "take for granted" these kinds of computations.

If I find I'm doing this repetitively, I find it more valuable to make a look-aside table that contains this kind of information in a precomputed form and then use triggers to keep it up to date. An example of this is a database where I roll-up the financial summary data from the invoice line-items to the quote level, then again to the submission level (which has multiple quotes) and then again to the policy level (which has multiple trees from multiple carriers).

It turns out that most of the queries really need the data at the policy level. By using triggers and summary tables I sped up some critical queries literally two orders of magnitude simply because the work was already done, while decreasing the performance of a saved change by an inconsequential amount. So if you find yourself doing a lot of summary queries, think about a way to avoid the work... but for simple cases I think inline calls to functions are fine.

Upvotes: 0

Dimitri
Dimitri

Reputation: 7013

In my opinion and from my experience inline functions are not necessarily evil. Sometimes i face a task that i couldn't achieve without using functions. If you are reusing the same exact routine over and over again in your stored procedures, then, just like in good old OOP you create a function and use it. But just for the sake of clean and short code I would not recommend using functions, if its one time use. The performance hit or improvement comes from table indexing and index maintenance. As long as the indexes are correctly created and maintained, inline functions are as good as writing plain sql statement.

Upvotes: 0

Related Questions