By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,705 Members | 2,017 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,705 IT Pros & Developers. It's quick & easy.

Using Scalar functions in views

P: n/a
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

Sep 19 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
(mc*****@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, es****@sommarskog.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
Sep 19 '07 #2

P: n/a
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*****@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
Sep 20 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.