0
\$\begingroup\$

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
Improve this question
\$\endgroup\$
3
  • 1
    \$\begingroup\$ 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. \$\endgroup\$
    – Pavlo Slavynskyy
    Sep 9, 2021 at 7:36
  • \$\begingroup\$ 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!? \$\endgroup\$
    – rohrl77
    Sep 9, 2021 at 7:55
  • \$\begingroup\$ 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'. \$\endgroup\$
    – rohrl77
    Sep 9, 2021 at 9:13

2 Answers 2

Reset to default
1
\$\begingroup\$

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.Windows_ID = @UserName
    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
Improve this answer
\$\endgroup\$
0
\$\begingroup\$

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!

Improve this answer
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.