Posted by Erland Sommarskog on 09/19/07 07:34

(mcleana@sympatico.ca) writes:
> I have a view that contains a complex query. A few of the columns
> call a function that returns a specific output. I also use a function
> to do a join as well.
>
> For example:
>
> SELECT l.ID, dbo.getStatus(l.ID) AS statusID
> FROM tableName A
> LEFT OUTER JOIN dbo.Status_LKP s ON dbo.getStatus(l.Leg_ID) =
> s.statusID
>
> For 800 records, this query takes about 9 seconds. I realize that for
> each record, the function is executed on a per row basis, so I am
> looking for alternatives.
>
> Does anyone know of other ways to accomplish something of the same?
> Basically I would like to include UDFs in a query and use those UDFs
> in the where and join clauses.

Scalar UDFs with data access in a join condition is sure recipe for poor
performance. You do best to incorporate the logic of the UDF in the view.

Also keep in mind that putting a column into an expression precludes the
usage of any index on that column.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]