Table valued function VS Stored procedure

Question

There's a function that :

• returns only 1 row
• has only 1 parameter
• has multiple joins on different tables, each only to return a single scalar value
• is called only once at each login of a web application

It currently is written in c# linq-to-sql and takes 150-300ms to execute.

After re-writing it in SQL using a very simple SELECT statement, it takes 30ms to execute.

Now I want to encase this select in a database function, what is the best (fastest execution) way to do so ?

1. Make a view and at every call do something like select * from viewXX where id=123
2. Make a stored procedure
3. Make a table-valued function

Are there any tricks I could take advantage of in such a case ?

Here is an example of what the query looks like

declare @id int=162;
select top 1 i.id, i.name, itd.description,
(select top 1 case when count(*)>0 then '1' else '0' end from category where category_desc=itd.description) "HasCategory",
(select top 1 number from wheels where wheel_item_id=i.id) "WheelCount"
from items i left items_desc itd on i.id=itd.itemid where i.id=@id

Answer 1

If the data changes infrequently, package the fast-running SELECT as a view and define an index on it.

An indexed view comes with some restrictions which minimally limits flexibility in future changes. There is nothing terribly bad, however.

If the data changes very often, and updates are performance-critical, the extra time to maintain the view may be a concern, but no more than for any other index.