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(*)>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