Q:

Very slow query in MSSQL 2016

I have a very slow procedule in mssql 2016, I dont think it should be that slow.
 ## the code ##
select @count = count(UID) from TABLE1 where IsProcess = 0 

while @count > 0 

begin

Declare @Name nvarchar(100)

Declare @UID int

select Top 1 @UID = UID, @Name = Name from Table1 where IsProcess = 0

set @UID2 = ISNULL((select TOP 1 UID from TABLE2 where Name like N'%' + @Name + N'%'), 0)

if @UID2 = 0 

begin

   insert into table2 (Name) values(@Name)

   set @UID2 = @@IDENTITY

end

insert into tablerelation (UID1, UID2) values(@UID, @UID2)

update TABLE1 set IsProcess = 1 where UID = @UID

select @count = count(UID) from TABLE1 where IsProcess = 0 

end 

there are about 5 million rows in table1
the server cpu was less than 10%
memory arround 4G (and 8G on server)
no other application run on server
it is now about 3 to 4 row per second 
I thought it should be 200 rows and up per second at least.
I had used cursor , it was the same
please help.
thanks a lot.
modified by code, this is my first post here, thanks for your patient

A:

Why are you using a loop instead of set-based operations?  From what I can tell, you want:
insert into tablerelation (uid1, uid2)
    select t1.UID, t2.UID
    from Table1 t1 cross apply
         (select top 1 t2.*
          from table2 t2
          where t2.name like N'%' + t1.Name + N'%'
         ) t2
    where IsProcess = 0;

This should be a bit faster, but it won't be an incredible speed-up.  The speed gain is from the reduction in database operations.  
However, the real problem is the join using like.  With wildcards, it is not very easy to speed it up.

