存储过程执行需要时间 [英] Stored Procedure is taking time in execution

问题描述

SQL Server面临一个非常奇怪的问题，我有一个存储过程，并且正在从C＃代码执行该过程。该过程将返回一个数据表/数据集。

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.

我的问题是从C＃代码/ ADO.NET代码执行过程花了太多时间（大约2分钟）。但是，当我从SQL Server执行相同的查询时，它在一秒钟内就会执行。

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.

我也尝试过使用相同的代码（旧过程代码）创建新过程，并且当我从ADO.NET执行此新过程时，不会花费很多时间。它正在用C＃第二次执行。

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.

推荐答案

我猜想从代码中调用过程时，您的执行计划不好。运行存储过程时，您将有一个不同的计划，因为默认情况下从SSMS运行时，计划签名是不同的。

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.

请参阅： http：/ /www.sommarskog.se/query-plan-mysteries.html 以获得一些可能的修复和清晰度。

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

如果您的过程参数繁重，则可能会使每次使用
option（recompile）;

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

这篇关于存储过程执行需要时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！