>>>>>>I agree with you naomi.
>>>>>>
>>>>>>Best broken down into sensible pieces.
>>>>>>
>>>>>>It looks like something thats had a lot bolted onto it over a long period.
>>>>>
>>>>>Hi Nicholas, I think that's exactly what has happened - I have just inherited it :-( - how could I break it down into smaller pieces ?
>>>>
>>>>Nick is on the right track. This inherited code is hard to understand. Break it down to its core, prove that works, then add pieces back until it breaks.
>>>>
>>>>Debugging can be drudgery. Scut work. It's rebuilding a wall brick by brick. Oddly enough, it's about my favorite part of software development. When you finally nail that b****d it's a great feeling. I have been known to do the Snoopy dance myself ;-)
>>>
>>>Hi there, the query works ok it's just a bit slow when the result set gets into the hundreds - I've actually improved it a bit by changing the order of the indexes on the letterhistory table
>>
>>Thats something I meant to ask yesterday (you've probably thought of this already). Is the query getting slow because something has changed in the data. In that case it might be worth thinking about redefining what the query is supposed to do and producing a new query
>
>Hi Nicholas, I think I'll remove all the letterhistory stuff and put it in it's own sp - it's a nightmare trying to restructure it as a whole

Hi Nicholas

This sp is not as complex as some I've seen. I saw a college professor's code where his main query fired an SP which contained another query. As the tables grew the app started to trip. End result was 1 million x 1 million reads. The query would timeout and cause the app to crash. By using a JOIN performance shot to the moon.