How about posting the proc and we'll start from there. In the meantime, verify that you're statistics are up to date.
-Mike
>I have a performance problem with SQL Server 2000. Here is the scenario:
>A DLL (on a webserver) executes a stored procedure (on a DB server) over a ODBC connection. Every now and than this DLL encounters Time Out problems (The time out is 1 minute) In that case I execute the sp in de Query Analyzer (no time out) and it works fine, but indeed very slow; like 10 minutes. If I execute the same sp again it finishes within 5 seconds. The DLL now works fine again until some other sp (or the same) start timing out.
>
>The only cause I can think of is Caching. Once SQL Server has the appropriate tables cashed the sp finishes faster. But the difference between 10 minutes and 5 seconds is very big.
>
>My questions are:
>* Are there other reason that could cause this behaviour?
>* How do I find out wich is the actual reason.
>* What can I do about this (beside adding memory or setting time out to 20 minutes)