Query performance guidance
3
votes
1
answer
110
views
I am working on a query that is running for reports and causing timeouts as it tends to run right over 30 seconds. I feel the UDFs are causing the issue, but I am not sure what can get me a positive gain in performance.
I have put the plan at:
https://www.brentozar.com/pastetheplan/?id=tUOdeVhykH
for review.
Any thoughts or help would be great.
sql
ALTER function [dbo].[ISM_fnGetUID_TS] ()
returns @Results table
(
UserID int --primary key
)
as -- body of the function
begin
insert @Results
select dbo.ISM_fnGetUID_Lite()
return
end
ALTER FUNCTION [dbo].[ISM_fnGetUID_Lite] ()
RETURNS int
AS
BEGIN
return (
select UserID = case
when app_name() like '%@App' then cast(left(app_name(), charindex('@', app_name()) - 1) as int)
else coalesce(
convert(smallint,substring(context_info(),1,2)),
(select UserID from dbo.TS_User u where (u.UserName = system_user and u.Disabled = 0)))
end
)
END
Asked by Garry Bargsley
(603 rep)
Jul 16, 2025, 12:42 PM
Last activity: Jul 18, 2025, 12:31 PM
Last activity: Jul 18, 2025, 12:31 PM