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

Paul White — Sun, 27 May 2012 05:32:10 GMT

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:

SELECT

MAX(t.Triple)

FROM dbo.Triples AS t

WHERE EXISTS

(

SELECT 1

FROM dbo.LargeTable AS lt

WHERE

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.

Paul