1) Any triggers on the table inserting into?

2) Any keys on the table inserting into?

3) Did you use sp_whoisactive to see how the query is performing...

HAH! Looks like our posts were being typed up at the same time, and you beat me by 5 minutes. Great minds think alike! 😎

In addition to other statements, I ALWAYS question this type of structure in temp objects in client code:

dataid int primary key identity(1,1),

In my experience...

select (
------Send Money
  select count(RECID) sum1
  from V_FBNK_FUNDS_TRANSFER001
  where TRANSACTION_TYPE = 'AC2U'
  AND HISTORY_NUMBER NOT IN (2)
  ) AS 'SendMoney1'
,
(select...

What data type is PLC Cd?

It would really help if you gave us more/all of the query and create table statement with some sample data.

With all the back and forth, I would like to reiterate that you REALLY should have IFI enabled (unless you have some REALLY esoteric security requirement).

pseudo code:

select
from tablea a
where not exists (select * from tablea b where a.guid = b.guid and b.reason <> reason.reason)


WHERE NOT EXISTS? Or possibly just a not equals.

Note I am just guessing here since you didn't provide CREATE table statements with INSERTs of sample data and your...

Without sample data and EXACTLY what is happening we may not be able to help.

What I do in debugging cases like this is scatter PRINT statements of various...

Another thing that should help performance (although not in computing a query plan) is to use explicit transactioning. Have a BEGIN TRAN/CHECK FOR ERROR/COMMIT OR ROLLBACK every 10K plus-or-minus statements.