Make sure all the columns on Object8 that your query references are either in the index key or the included columns, this will stop it doing a key lookup for...

Hi Scott.... revisiting your code above.... I'm getting a "Msg 8120, Level 16, State 1, Line 24 Column 'alias1.CALL_TIME_SECS' is invalid in the select list because it is not...

 FROM a2wh.dbo.CallLogCommon com with (NOLOCK)
 JOIN a2wh.dbo.Campaigns ud with (NOLOCK)
   ON com.[campaign] = ud.[campaign]
CROSS APPLY dbo.itvfGetSeconds(Call_Time) ctsec
CROSS APPLY dbo.itvfGetSeconds(Talk_Time) ttsec

Same as Scott's but can use BETWEEN instead of two ANDs and can perform the subtraction in another CROSS APPLY.

Also, it is good practice to always have an ELSE in...

although going through the route of linked server best practices would mean that the linked server would not be named as the server name but rather as a logical...

Linked servers will be the easiest way. Then you just add the server name as a prefix:

SELECT *
  FROM [ServerName].[DatabaseName].[SchemaName].[TableName]

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver15

These are your subqueries

           ,(SELECT RS.LIB_SERVICE 
          ...

One of the major ideas of client/server architecture was that the system would come in tiers. The database layer would get a result set, then pass that set on...

If you want the SQL optimised you would be better off giving us the actual SQL that is generated along with the execution plan.

The optimiser should make them have the...

Set up the data:

if object_id('tempdb..#a','U') is not null
    drop table #a

if object_id('tempdb..#b','U') is not null
    drop table #b
go

create table #a 
(
...

I know it's a temporary...

Your database is not normalised.

The standard way to store this is to have two tables instead of your #u table

CREATE TABLE #User
(
    UserID int...

I need to count the number of times each surgeon has done a procedure where op1 to op4 includes procedure b AND d OR just d irrespective of any...

Or you could...