I was just reading about Stored Procedure performance and I came to this blog. I observed that in the below mentioned query -

select Type, OrigionalLogID, (select top 1 b.field from table b where b.type=a.type and b.OrigionalLogID=a.OrigionalLogID and TranType in ('these', 'change') order by b.field desc), .... into @table from table a where .... order by OrigionalLogID

I could see your query has co-related sub query , hence the inner query will execute every time for each row in the table. Which makes this query slow.

It would help if the above query is rewritten as -

select Type, OrigionalLogID

into @table

from table a

where exists

(

select top 1 field

from table as b

where b.type = a.type and b.OrigionalLogID = a.OrigionalLogID and b.TranType in ('these', 'change') order by b.field desc

)

where ....

order by OrigionalLogID