Hi all, first post be gentle.
I have a few pieces of code which include Updates from temp tables, and loop around to perform multiple updates. It does this as it requires the previous set of data to build up the next set (it's pushing data around a road network). After the 10th loop it dumps the results into another temp table then attempts to update the main final table. I've also tried a cut down version of the code which excludes the 10th looping part but I come along the same problem, just once it's processed more days' worth of data. The behaviour is like a log file (not the trans log) is filling up and causing a bottleneck.
This works fine until it seems to hit a certain relatively predictable point where the query changes from roughly 45 seconds to complete a full daily run, to 9-10 minutes and stays like this forever. The only way I seem to be able to resolve this is to drop the data into another 'middle' table, wait until the 'bug' appears, stop the query, transfer the data to the final table and truncate the middle table. Once truncated the query runs at normal speed again on the middle table. Needless to say this is stupid and annoying.
I'm not a DBA and don't have access to actual execution plans as restricted by the corporate security policy. So I'm looking to see if anyone has seen this kind of behaviour before and knows the answer. The simpler code is 350 lines long which I've attached as a text file. The simpler code might also look like it doesn't need the looping if you're better at SQL than me, and the longer version which is 1300ish lines has 2 levels of loops (not attached) as it draws in more sources.