Joins vs Functions and Performance

Question

Joins vs Functions and Performance

Don Thompson 0

It is axiomatic that joining to another table from your primary table to retrieve data, using primary keys, provides better performance than using a function. At least that's what I know from the last time I cared about it years ago. It's time to care about it again.

Assume a primary table. In a query on this table, you want to include a tidbit of info from other table(s). I expect that if there is a primary key relationship between the two tables then a join is the way to go.

But if that tidbit is buried in many tables with complex joins, then I would think a function is a better way. But what does SQL Server think about it?

Is it better, performance wise, to throw all those other tables into the query with various complex joins or just create a function to "black box" that mess and simply provide an argument to retrieve the tidbit.

Of course there are many factors to consider that I am not including. Just looking for a rule of thumb.

3 answers

Your answer

Answer 1

From a performance point-of-view, writing the code directly against the tables is usually the better choice.

As for involving function, it depends on what sort of function we are talking about. If it is an inline table-function, this is essentially a parameterised view, and as such it does cause any overhead, since a view/inline table function is just a macro that is pasted into the query, and the optimizer works with the expanded query text. There is a caveat though: It could be that the view involves more tables needed for the operation, or that you need a column from one of the tables that is not exposed in the view. This may lead to that you join in the table explicitly, and now there is overhead.

For multi-statement table function and scalar functions, they typically always add overhead, since the function is an execution on its own, and a scalar function is typically executed row-by-row. True, since SQL 2019, scalar functions may be inlined, but there are many cases where this does not happen, so it is nothing you can assume.

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 3

Hi @Don Thompson,

Thank you for reaching out to the Microsoft Q & A Forum.

When you join tables using indexed columns, SQL Server can leverage index seeks, hash joins, or merge joins. The optimizer sees the entire query and can reorder joins, push predicates, and parallelize execution. Joins are generally set-based, which SQL Server is optimized for.

Scalar or multi-statement table-valued functions (TVFs) often act like black boxes to the optimizer. Scalar functions are evaluated row-by-row, which can lead to Row-By-Agonizing-Row behavior. Multi-statement TVFs don’t expose statistics to the optimizer, so SQL Server assumes 1 row returned, which can cause poor plans. Inline TVFs are better because they behave like views and allow the optimizer to inline them into the query.

Performance Rule of Thumb:

If the tidbit comes from a single table or a few tables with proper indexes--> Use JOINs.
If the logic is complex but can be expressed in a set-based way --> Use JOINs or inline TVFs.
Avoid scalar functions in SELECT or WHERE clauses for large datasets they kill performance because they run once per row.
Multi-statement TVFs are also risky for large sets because of poor cardinality estimates.

Note: Rule of Thumb:

Joins --> Inline TVFs --> Multi-statement TVFs --> Scalar Functions

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

Share via

Joins vs Functions and Performance

3 answers

Your answer