Using Scalar functions in views

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.

Thanks

Re: Using Scalar functions in views

(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Using Scalar functions in views

If you need a UDF to be able to join to another table, then you have not
designed your schema properly. An ID does not come out of thin air. If
you have a relation between tables, you should register the foreign key
properly. So not only is it a bad idea to do this from a performance
point of view, but also from a design point of view.

If dbo.getStatus does a lookup in a related table, then you could simply
join with that table. If you don't want to write such an extra join, you
can move it to a view and select from the view (instead of the table).

--
Gert-Jan


mcleana@sympatico.ca wrote:
>
> 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.
>
> Thanks