Re-write as an EXISTS, rather than IN sub-query:
UPDATE #branch_usersSET greenlight = 1FROM #branch_users As BWHERE 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_A WHERE DB.branchid = B.branchid)
Probably best to post the DDL of all tables/views involved.
What size is the loginlog table ? It has a clustered index scan which migth be costly depending on its size.
Just select the objects in EM and generate a SQL script, setting the options to script keys & indexes.
A table of 2+ million rows being scanned is definitely not desirable. Finding a way to get the optimiser to perform an index seek on the loginlog table is bound to improve things.
According to your profiler trace, the pepper doesn't turn to fly specs until the appian.dbo.udf_A User Defined Function hits the fan... that's where your problem is and you should probably post the code for THAT. If you don't think so, tell me where in the script you submitted that dealer regions are found and the IsActive flag is interrogated...
Clause in your UDF appian.dbo.udf_A
"WHERE ... datediff(day, [login_log].[dt_login], Convert([@date]))<=[@last_x_day"
eliminates all indexes and makes server scan whole table and return all rows, because it cannot predict result of the calculation.
Change it to
[login_log].[dt_login]>= datediff(day, - @last_x_day, Convert([@date]))
and make sure you have clustered index on column dt_login.