Table valued function VS Stored procedure 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 ?


Make a view and at every call do something like  select * from viewXX where id=123
Make a stored procedure
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(*)&gt;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
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.