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?
I had 4 parameters going in to the Stored procedure. In the query I had declared and set them at the top so I could easy copy/paste when I finished.
I prefixed all the variables coming in to the stored procedure with an o declared the variables in the stored procedure and set them equal to the o prefixed variables and the stored procedure ran as expected.
again THANK YOU. I'll have to try to remember this in case I ever run into it again. For now I'm off to fix the o variables to make sense.
We walk in the dark places no others will enterWe stand on the bridge and no one may pass