That doesn't make any difference in the query performance.

The query is still performing TABLE SPOOLS and LAZY SPOOLS.

Any other ideas?

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE@last_x_days int

SELECT @last_x_days = 7

DECLARE @date smalldatetime

SELECT @date = getdate()

--NEXT, FILL TEMP TABLE WITH ACTIVE USERS (USERS THAT HAVE LOGGED IN WITHIN THE PAST x DAYS)

UPDATE #branch_users

SET greenlight = 1

FROM #branch_users As B

WHERE EXISTS (

SELECT *

FROM appian.dbo.users USERS

JOIN dealerinfo.dbo.v_DealerBranches DB ON

DB.dlrcode = USERS.dlrcode

JOIN dealernet.dbo.v_loginlog LL ON

LL.auid = appian.dbo.udf_AUID(USERS.user_id)

WHERE DB.branchid = B.branchid

AND (USERS.is_active = 1) -- and users.approved=1)

AND ( datediff(d,LL.dt_login,@date) <= @last_x_days

AND datediff(d,@date,LL.dt_login) < 1))

(3 row(s) affected)

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Table Update(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001D9]), SET: ([#branch_users].[greenlight]=1))

|--Top(ROWCOUNT est 0)

|--Nested Loops(Left Semi Join, OUTER REFERENCES: (.[branchid]))

|--Table Scan(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001D9] AS ))

|--Row Count Spool

|--Nested Loops(Inner Join, WHERE: ([appian].[dbo].[udf_AUID]([login_log].[user_id])=[appian].[dbo].[udf_AUID]([USERS].[user_id])))

|--Nested Loops(Inner Join, WHERE: ([DlrCodes].[DlrCode]=Convert([USERS].[dlrcode])))

| |--Nested Loops(Inner Join)

| | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Branches].[PK_Branches]), SEEK: ([Branches].[BranchID]=.[branchid]) ORDERED FORWARD)

| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[DlrID]))

| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[RegionID]))

| | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrCodes].[DlrPrefix]))

| | | | |--Index Spool(SEEK: ([DlrCodes].[BranchID]=.[branchid]))

| | | | | |--Clustered Index Scan(OBJECT: ([dealerinfo].[dbo].[DlrCodes].[PK_DlrCodes]))

| | | | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[DlrPrefixes].[PK_DlrPrefixes]), SEEK: ([DlrPrefixes].[DlrPrefix]=[DlrCodes].[DlrPrefix]) ORDERED FORWARD)

| | | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Regions].[PK_Regions]), SEEK: ([Regions].[RegionId]=[DlrPrefixes].[RegionID]) ORDERED FORWARD)

| | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[Dealers].[PK_Dealers]), SEEK: ([Dealers].[DlrID]=[DlrPrefixes].[DlrID]) ORDERED FORWARD)

| |--Table Spool

| |--Clustered Index Scan(OBJECT: ([appian].[dbo].[users].[PK_users] AS [USERS]), WHERE: (Convert([USERS].[is_active])=1))

|--Table Spool

|--Clustered Index Scan(OBJECT: ([appian].[dbo].[login_log].[PK_login_log]), WHERE: ((([login_log].[site_id]='MSDNET' OR [login_log].[site_id]='PMDNET') AND datediff(day, [login_log].[dt_login], Convert([@date]))<=[@last_x_day

(21 row(s) affected)