DBA 服务器中级开发 SQL

存储过程不会提高性能

Shivprasad koirala

4.76/5 (71投票s)

2012年7月2日

CPOL

6分钟阅读

519001

许多开发人员认为存储过程是预编译的，因此速度更快。我有一个不同的故事要讲。

引言

我假设您点击这篇文章/博客是因为您和我一样是存储过程的忠实粉丝，并且您无法看到这类含糊的陈述。我诚挚的建议是，请先阅读这篇文章，认真思考一下实验结果，然后再在评论区随时向我“扔砖头”。

存储过程是预编译的，因此它们更快

问任何一个人为什么他更喜欢存储过程而不是内联查询，大多数人都会给出一个标准的回答：

“存储过程是预编译和缓存的，因此性能要好得多。”

让我用图表来解释一下上面的句子。当我们第一次执行 SQL 时，会发生三件事：

检查 SQL 语法是否错误。
选择最佳的 SQL 执行计划（选择使用聚集索引、非聚集索引等）。
最后执行 SQL。

上面的陈述表明，当您第一次运行存储过程时，它将经过以上所有步骤，并且计划将被缓存到内存中。因此，下一次执行存储过程时，它只需从缓存中获取计划并执行相同的操作。这样可以提高性能，因为前两个步骤完全被消除了。

上面的陈述还说/暗示，对于内联查询，以上所有步骤都会一次又一次地重复，从而大大降低了性能。

上面的解释对于旧版本的 SQL Server 来说是相当有效和真实的，但从 2005 年开始，所有 SQL 语句，无论它来自内联代码、存储过程还是其他任何地方，都会被编译和缓存。

好的，现在请你兑现承诺

图片来源：http://www.stegman.com/site/wp-content/uploads/2011/07/Man-walking-rope.jpg

为了证明上述观点，我做了一些实验。我写了一个简单的 .NET 应用程序，它使用两种方法调用 SQL Server：一种是简单的内联 SQL，另一种是存储过程。

以下是一个简单的实验来证明这一点。

我们创建了两种场景：一种场景运行一个简单的内联 SQL，如下所示。该 SQL 查询一个简单的“Users”表，以检查用户是否存在于数据库中。

SqlCommand objCommand = new SqlCommand("Select * from Users where UserName='"
                                        + UserName + "' and Password='"
                                        + Password + "'", objConnection);

在第二种场景中，相同的内联 SQL 被封装在一个名为“sp_login”的存储过程中。

SqlCommand objCommand = new SqlCommand("sp_Login", objConnection);
objCommand.Parameters.Add(new SqlParameter("UserName", UserName));
objCommand.Parameters.Add(new SqlParameter("Password", Password));
objCommand.CommandType = CommandType.StoredProcedure;

这两个 SQL 都从应用程序中执行，并在后台运行分析器。我们通过运行分析器捕获两个事件：CacheHit 和 CacheInsert。当计划被插入缓存时，会触发 CacheInsert 事件；当计划被从缓存中使用时，会触发 CacheHit 事件。

当我们运行带有存储过程的实验时，我们看到了以下结果。您可以在下面的跟踪中看到：

“CacheInsert”首先创建计划并将其插入缓存。计划缓存后，会发生 CacheHit 事件，这意味着它从缓存中获取了计划，而不是从头开始重新创建。

当我们运行带有内联 SQL 的实验时，我们看到了类似的结果。您可以看到 CacheHit 事件是在 CacheInsert 事件触发之后发生的。

作弊者，改变数据？

如果您仔细查看之前的实验，数据是完全相同的。当我更改如图所示的数据时，您可以看到它不再使用缓存，而是创建新的缓存条目。

我将修改 ADO.NET 代码以支持参数，如下所示。

SqlCommand objCommand = new SqlCommand(
   "Select * from Users where UserName=@userName and Password=@Password", objConnection);
objCommand.Parameters.AddWithValue("@userName", UserName);
objCommand.Parameters.AddWithValue("@Password", Password);

当我捕获分析器中的缓存事件时，它正在使用缓存。您可以在下图看到，首先发生缓存插入事件，之后它总是命中缓存以获取计划，而不是重新创建。

动态 SQL 和动态 SQL

人们使用的最令人困惑的术语之一是动态 SQL。让我们进一步细化这个词。动态 SQL 有两种类型：一种是动态 SQL，另一种是参数化动态 SQL。

来源：蜘蛛侠3

动态 SQL 的形式如下（它可以更动态，其中列名也是动态构建的）。

SqlCommand objCommand = new SqlCommand("Select * from Users where UserName='"
                                        + UserName + "' and Password='"
                                        + Password + "'", objConnection);

上面的动态 SQL 可能不会使用缓存中的计划，除非自动参数化有所帮助（http://msdn.microsoft.com/en-us/library/aa175264(v=sql.80).aspx）。

如果您使用下面的参数化动态 SQL，它将使用来自缓存的 SQL 计划，就像存储过程一样。

SqlCommand objCommand = new SqlCommand("Select * from Users where UserName=@userName and Password=@Password", objConnection);

objCommand.Parameters.AddWithValue("@userName", UserName);
objCommand.Parameters.AddWithValue("@Password", Password);

简单来说，内联参数化 SQL 的性能与存储过程相同。

嗯，网络流量呢？

如果您一直读到这里，您一定感到尴尬，就像我输掉这场辩论时一样。为了反驳，许多开发人员还会争辩说，与内联 SQL 相比，使用存储过程时的网络流量会减少。

简单来说，如果我们使用存储过程，我们只发送

Sp_login

如果我们使用内联 SQL，我们会发送完整的 SQL，这将增加流量。

'Select * from Users where UserName=@UserName and Password=@Password'

如果我们有很多 Windows 应用程序连接到一个 SQL Server，这可能是一个有效的观点。如果事务很多，这可能会导致大量网络流量。

在 Web 应用程序的情况下，SQL 和 ASP.NET 代码（在同一个数据中心）非常靠近，我并不完全相信这个观点。

如前所述，这仅仅是我的个人观点。

我仍然会使用存储过程

归根结底，我仍然会选择存储过程。选择存储过程的理由**不是性能**，而是更多地出于安全性和可维护性的考虑。以下是存储过程相对于内联 SQL 的一些优点：

抽象

将所有 SQL 代码放入存储过程中，您的应用程序将完全抽象出字段名、表名等。因此，当您在 SQL 中进行更改时，对 C# 代码的影响会更小。

安全

这是存储过程得分最高的部分，您可以为用户和角色分配执行权限。

易于维护

现在，因为我们已经集中管理了存储过程，任何问题，如修复缺陷和其他更改，都可以在存储过程中轻松完成，并且会在所有已安装的客户端中得到反映。至少我们不需要编译和部署 DLL 和 EXE。

集中调优

如果我们知道有一个运行缓慢的存储过程，我们可以隔离它，DBA 团队可以单独对其进行性能调优。

游标、临时表复杂性

简单的 T-SQL 语句是可以的。但是，如果您有一系列包含 IF、ELSE、游标等的语句怎么办？在这种情况下，存储过程再次非常有用。