This has probably been discussed a lot before, but just in case here it is again, possible solution for following scenario:
You’re calling SQL Server stored procedure from your .NET code and it runs extremely slow. When you run same SP with exactly the same parameters (as captured by SQL Server Profiler) directly in SQL Server Management Studio, it runs very fast. What gives?
Chances are – that SP was executed before and query plan was cached for the specific parameters. To avoid this add WITH RECOMPILE option to your CREATE PROCEDURE or ALTER PROCEDURE statement. This will force SQL Server to create a new query plan every time SP runs, perhaps adding slight overhead, but creating an optimized path that will cover that overhead tenfold.