THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

T-SQL User-Defined Functions: the good, the bad, and the ugly (part 2)

This blog has moved! You can find this content at the following new location:

Published Saturday, May 26, 2012 5:56 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Paul White said:

Hi Hugo,

I realise the main point of this post is to show the poor performance characteristics of scalar UDFs that access data (and associated costing limitations) but you expanded the discussion to optimizer stuff, so I have some comments... :)

The small sizes of your example tables do not prompt a full effort from the optimizer.  Faced with relatively small costs for obvious plans, it terminates its search early with Good Enough Plan Found, which is a good thing in general.

The point about trying different syntax (while being careful about semantics!) is a good one.  The optimizer will never include all possible logical transformations, just a good set that produce benefits for a wide range of common queries (trading ultimate plan quality for compilation time).

On that note, a useful and natural way to express the query requirement is:



FROM dbo.Triples AS t




   FROM dbo.LargeTable AS lt


       lt.DataVal = t.Value


This produces a plan with the pre-aggregation you are looking for, but we can do even better by adding the obvious index on LargeTable (DataVal):

CREATE NONCLUSTERED INDEX nc1 ON dbo.LargeTable (DataVal);

That gives us a plan with no aggregation at all, just two seeks, a semi-join, and a stream aggregate.  The estimated cost is 0.0305208, compared with 0.296198 for the manual rewrite using DISTINCT.

Though not immediately important in the specific case you show, we could also help the optimizer by creating a UNIQUE constraint on Triples (Triple), and enforce a FOREIGN KEY relationship from LargeTable (DataVal) to Triples (Value).

I generally prefer APPLY to subqueries (a sub-SELECT in an outer SELECT clause).  APPLY tends to optimize better in my experience, especially when useful FOREIGN KEY and NOT NULL constraints are present.


May 27, 2012 1:32 AM

Leave a Comment


About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement