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?

thanks,

  Kevin

Lynn Pettis

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 enter
We stand on the bridge and no one may pass