Sample Header Ad - 728x90

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