Stored Procedure is taking time in execution

问题:

I am facing a very strange issue with SQL Server that I have a stored procedure and I am executing the procedure from C# code. The procedure will return a datatable / dataset.

My problem is that procedure is taking too much time in execution from C# code / ADO.NET code (around 2 minutes). But when I execute the same query from SQL Server, it's executing within a second.

Also I have tried by create new procedure with the same code (old procedure code) and when I am executing this new procedure from ADO.NET it's not taking much time. It's executing withing a second time in C#.

So I am not getting what is the issue with my old procedure.

回答1:

I am guessing that you have a bad execution plan when the procedure is called from code. When you run the stored procedure, you will have a different plan because the plan signature is different when running from SSMS by default.

see: http://www.sommarskog.se/query-plan-mysteries.html for some possible fixes and clarity.

If your procedure is parameter heavy, it may make sense to compile every time using option(recompile);