Dean Cochrane (3/9/2009)
It's recompiling because the schema has changed. This may be due to declaring temp tables partway through the procedure. At least, that's where I've run into this issue. See if moving all temp table declarations to the beginning of the proc stops the recompilations. At least then you'll only have to take the compilation hit once.
Well I know it will recompile based on the table activity. I can't understand the 3 minute recompile though. I did catch other recompile events in my trace but only when this sequence above is encountered do we have problems. We are using a temp table and I think we can safely move to a table variable. So I might try that and see if it helps. Thanks.