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