michael ham (3/9/2009)
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.
Be careful with table variables, there is some limitations as you cannot index them and it does not keep statistics : http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
I don't know if your temp table table was indexed at all, but it could lead to severe performance issues.
I thought table variables were memory only, but recently found this article, and it proved me wrong. Just saying it is good to know.
Cheers,
J-F