Using Scalar functions in views

[mcleana]

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

[Erland Sommarskog]

(mc*****@sympat ico.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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Gert-Jan Strik]

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
mc*****@sympati co.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