This is an odd one I just ran into today. I rebuilt an older query that aggregated together a bunch of information. The query had slowed down to the point that it was regularly timing out.
The primary table that is being accessed is about 80k records so it's not a huge data set.
To optimize the query I removed a table variable that worked similar to this (with more column)
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 built a view that used min/max(case ...) to build the same data. Doing that dropped a considerable amount of time off of the execution time of the query and simplified things considerably.
I then rebuilt fields that were selected as (select) as fieldname into count(case ...) statements to speed things even further. I managed to drop the query that ran for 60 seconds for a month of data to run in about 2 seconds for a month, and about 7 seconds for a full history.
I then took the query I had built and copied it into a stored procedure created the procedure and executed with the same parameters I was using for test and the stored procedure takes over 2 min to execute (it generates the exact same execution plan)
Does anyone have any idea what could be breaking or how I could fix it?