# Table-Value Function with row-level-locking

I'm implementing row-level-locking on a MS SQL Server. The function I created below works, but even with a very small data set, it takes a significant amount of time to return the results.

If I were to code this in VBA for example, I would call the username() function only once and store the result. Can this be done in a table-valued function?

How can I improve this function to perform faster?

CREATE Function [compliance].[fn_AllowActivity] (@ActivityID sysname)
returns table
with schemabinding
as
return select 1 as [fn_AllowActivity_result]
from
compliance.tbl_Users join compliance.tbl_Activity on compliance.tbl_Users.UnitID=compliance.tbl_Activity.UnitID
where @ActivityID=compliance.tbl_Activity.ActivityID and compliance.tbl_Users.Active=1 and compliance.tbl_Users.Windows_ID=substring(user_name(),CHARINDEX('\',user_name())+1,10)
or compliance.tbl_Users.Windows_ID = substring(user_name(),CHARINDEX('\',user_name())+1,10) and compliance.tbl_Users.Active=1 and compliance.tbl_Users.RoleID>=3
GO

• Are tbl_Users.UnitID and tbl_Activity.ActivityID primary keys? Are tbl_Activity.UnitID, tbl_Users.Active and tbl_Users.RoleID indexed? Also, you check compliance.tbl_Users.Active=1 twice. Sep 9 '21 at 7:36
• tbl_Users.UnitID and tbl_Activity.ActivityID are primary keys. None of the other columns are indexed. The reason i'm checking activity twice is that there are two types of conditions i'm checking... hence the OR clause... but I guess you are saying that is not what i'm doing!? Sep 9 '21 at 7:55
• I have added indexes on the columns you suggested as well as other ones that are refrenced. It has helped to greatly speed up execution time. Although, there are still some queries that are very slow. I'm now analyzing these with the 'execution planer'. Sep 9 '21 at 9:13

Aside from the indexes (which is important), you can improve the query by storing the username in a variable and adding the username condition in the join, which would simplify the query and improve the join performance :

CREATE Function [compliance].[fn_AllowActivity] (@ActivityID sysname)
RETURNS TABLE
WITH schemabinding
AS

DECLARE @UserName VARCHAR(250) = SUBSTRING(user_name(),  CHARINDEX('\', user_name() ) + 1 , 10)

RETURN SELECT 1 AS [fn_AllowActivity_result]
FROM
compliance.tbl_Users
JOIN compliance.tbl_Activity
ON compliance.tbl_Users.UnitID = compliance.tbl_Activity.UnitID
AND compliance.tbl_Users.Active = 1
WHERE
@ActivityID = compliance.tbl_Activity.ActivityID
AND compliance.tbl_Users.Active = 1
OR  compliance.tbl_Users.RoleID >= 3
GO


Based on Pavlov's comment I added indexs to my tables and altered the fuction as follows:

ALTER Function [compliance].[fn_AllowActivity] (@ActivityID sysname)
returns table
with schemabinding
as
return select 1 as [fn_AllowActivity_result]
from
compliance.tbl_Users left join compliance.tbl_Activity on compliance.tbl_Users.UnitID=compliance.tbl_Activity.UnitID
where compliance.tbl_Users.Active=1 and compliance.tbl_Users.Windows_ID = substring(user_name(),CHARINDEX('\',user_name())+1,10) and
(@ActivityID=compliance.tbl_Activity.ActivityID or compliance.tbl_Users.RoleID>=3)
GO


Performance is now within what is expected. Many thanks!