存储过程与内联SQL

#1 楼

我相信这一观点在某一时刻是正确的，但在当前版本的SQL Server中却不是。整个问题是，在过去，临时SQL语句无法正确优化，因为SQL Server只能在批处理级别进行优化/编译。现在我们有了语句级的优化，因此来自应用程序的适当参数化的查询可以利用与存储过程中嵌入的查询相同的执行计划。

我仍然更喜欢DBA中的存储过程出于以下原因（其中一些会对性能产生巨大影响）：


如果我有多个重复使用相同查询的应用，则存储过程将封装该逻辑，而不是在不同的代码库中乱扔相同的临时查询。重复使用相同查询的应用程序也可能受到计划缓存膨胀的影响，除非它们被逐字复制。即使大小写和空格之间的差异也可能导致同一计划的多个版本被存储（浪费）。
我可以检查和解决查询的问题，而无需访问应用程序源代码或运行昂贵的跟踪以查看
我还可以控制（并事先知道）应用程序可以运行哪些查询，可以访问哪些表以及在何种上下文中等等。如果开发人员正在临时编写查询，他们的应用程序，或者他们每次需要访问我不知道或无法预测的桌子时，或者如果我不太负责任/热情和/或缺乏安全性时，就不得不拖着我的衬衫袖子意识，我只是要将该用户提升为dbo，以便他们不再烦扰我。通常，当开发人员人数超过DBA或DBA固执时，才执行此操作。最后一点是我们的缺点，我们需要在提供所需查询方面做得更好。
与此相关的是，一组存储过程是一种非常简单的方法，可以准确地盘点系统上可能正在运行的查询。一旦允许应用程序绕过过程并提交自己的即席查询以查找它们，我就必须运行一个覆盖整个业务周期的跟踪，或者解析所有应用程序代码（同样，我可能无权访问）以查找任何看起来像查询的内容。能够查看存储过程的列表（并grep单个源sys.sql_modules，用于引用特定对象）使每个人的生活变得更加轻松。
我可以花更多的时间来防止SQL注入；即使我接受输入并使用动态SQL执行它，我也可以控制很多允许发生的事情。我无法控制开发人员在构造内联SQL语句时的工作。
我可以优化查询，而无需访问应用程序源代码，进行更改的能力以及对应用程序语言的了解，有效地执行此操作，将重新编译和重新部署应用程序的权限（不必担心），等等。如果分发了该应用程序，这尤其成问题。
我可以在存储过程中强制某些设置选项来避免个别查询受SSMS中应用程序中某些缓慢的问题的影响？问题。这意味着对于两个调用临时查询的不同应用程序，一个可能具有SET ANSI_WARNINGS ON，另一个可能具有SET ANSI_WARNINGS OFF，并且每​​个应用程序都有自己的计划副本。它们获得的计划取决于所使用的参数，适当的统计信息等。在每种情况下，首次调用查询都可能导致不同的计划，从而导致非常不同的性能。
与某些ORM不同，我可以控制诸如数据类型和参数使用方式之类的东西-诸如EF之类的某些早期版本会根据参数的长度对查询进行参数化，因此如果我有一个参数N'Smith'和另一个N'约翰逊，我将获得该计划的两个不同版本。他们已解决此问题。他们已经解决了这个问题，但还有什么要解决的呢？
我可以做ORM和其他“有用的”框架和库尚不支持的事情。

这个问题很可能引发比技术辩论更多的宗教争论。如果看到这种情况，我们可能会关闭它。

#2 楼

TLDR：只要对内联sql进行了参数化，两者之间就不会有明显的性能差异。这是我逐渐淘汰存储过程的原因：


我们运行一个“测试版”应用程序环境-与生产环境平行的环境，共享生产数据库。因为db代码是在应用程序级别上，并且很少发生db结构更改，所以我们可以允许人们确认QA以外的新功能，并在生产部署窗口之外进行部署，但仍提供生产功能和非关键性的修补程序。如果一半的应用程序代码在数据库中，这将是不可能的。


我们在数据库级别（octopus + dacpacs）练习devop。但是，虽然基本上可以清除和替换业务层及上层，然后进行相反的恢复，但对于必须进行数据库的增量和潜在破坏性更改而言，情况并非如此。因此，我们希望使数据库部署更轻松，更不频繁。


为了避免相同代码的几乎完全相同的可选参数副本，我们经常使用'where @var为null或@ var = table.field'模式。使用存储的proc，尽管意图非常不同，但您可能会获得相同的执行计划，因此会遇到性能问题或使用“重新编译”提示消除缓存的计划。但是，通过简单的代码在sql末尾添加“签名”注释，我们可以基于变量为空的方式强制执行不同的计划（不要将其解释为所有变量组合的不同计划-仅null与不为null）。更新8/2020-这一点似乎不再是正确的，或者现在变得很难，因为更高版本的sql server已变得非常聪明，可以忽略琐碎的代码。


我只需对SQL进行少量更改就可以对结果进行重大更改。例如，我可以使用一条包含两个CTE的语句“ Raw”和“ ReportReady”。没有什么可以说必须使用两个CTE。我的sql语句可以是：
...
从* {{format}}中选择*“


这使我可以使用完全相同的业务逻辑方法对于简化的api调用和需要更详细的报告，以确保我不会重复复杂的逻辑。

当您具有“仅处理”规则时，最终会遇到很多麻烦最终导致CRUD的绝大多数SQL中都存在冗余-您绑定所有参数，在proc签名中列出所有这些参数，（现在您在不同项目中的其他文件中），则可以映射这些简单参数添加到其列中。这会产生相当脱节的开发体验。

使用procs的理由很充分：


安全性-您在这里有了另外一层该应用程序必须通过。如果不允许应用程序服务帐户触摸表，而仅对proc具有“执行”权限，则您将获得一些额外的保护。交流电


重用-虽然我会说重用应该主要在业务层进行，以确保您不绕过非数据库相关的业务规则，但我们仍然具有临时性的，低级的“随处可见”类型的实用程序proc和函数。 br />

重用-我在上面提到的是“加号”，但在这里也想提一下重用应该在业务层进行。当业务层也可能正在检查其他非数据库服务时，不应将插入记录的proc视为“可重用”。


缓存计划膨胀-这将是一个问题的唯一方法是，如果您串联值而不是参数化。每个语句很少获得多个计划的事实实际上常常在您在查询中带有“或”时对您造成伤害。


语句大小-在proc名称上多出了kb的sql语句相对于返回的数据而言，通常可以忽略不计。如果对实体来说可以，那么对我也可以。


查看确切的查询-使查询易于在代码中找到就像将调用位置作为注释添加到代码中一样简单。使代码从c＃代码复制到ssms就像一些创造性的插值和注释用法一样容易：

      //Usage /*{SSMSOnly_}*/Pure Sql To run in SSMS/*{_SSMSOnly}*/
      const string SSMSOnly_ = "*//*<SSMSOnly>/*";
      const string _SSMSOnly = "*/</SSMSOnly>";
      //Usage /*{NetOnly_}{InterpolationVariable}{_NetOnly}*/
      const string NetOnly_ = "*/";
      const string _NetOnly = "/*";

Sql注入-参数化查询。做完了如果proc改为使用动态sql，则实际上可以撤消该操作。


绕过部署-我们也在数据库级别上实践devop，因此这不是我们的选择。


“应用程序速度慢，SSMS速度快”-这是一个计划缓存问题，会影响到双方。 set选项仅导致编译新计划，该计划似乎可以解决“一组变量”的问题。这只能回答您为什么会看到不同的结果的原因-设置选项本身不能解决参数嗅探的问题。


不缓存内联sql执行计划-只是错误。像proc名称一样，参数化的语句会快速哈希，然后通过该哈希搜索计划。它是100％相同。 />

https://weblogs.asp.net/fbouma/38178
https://stackoverflow.com/a/15277/852208

#3 楼

我尊重提交者，但我谦虚地反对所提供的答案，并非出于“宗教原因”。换句话说，我认为Microsoft没有提供任何可减少使用存储过程指南的需求的工具。

提供给开发人员的任何支持使用原始文本SQL查询的指南都必须包含许多警告，以便我认为最谨慎的建议是大力鼓励使用存储过程，并且不鼓励使用您的开发人员团队可以从事在SQL SPROC（存储过程）之外进行在代码中嵌入SQL语句或提交原始的，纯文本的基于SQL的SQL请求的实践。

我认为简单的答案提交人推测为什么要使用SPROC：SPROC被解析，优化和编译。这样，由于您保存了查询的静态表示，因此缓存了他们的查询/执行计划，通常，您只能通过参数来更改它，而对于复制/粘贴的SQL语句（可能会变体）而言，情况并非如此从页面到页面以及组件/层，并且经常变化到可以在呼叫之间指定不同的表，甚至是数据库名称的程度。根据一些非常严格的规则，允许这种类型的动态临时SQL提交，大大降低了DB Engine为您的临时语句重新使用查询计划的可能性。在这里，我将动态即席查询（按照提出的问题的精神）与使用有效的System SPROC sp_executesql进行区分。 />

不包含用户上下文并允许DB引擎重用的串行和并行查询计划。
执行上下文，允许新用户以以下方式重用查询计划：不同的数据参数。
过程高速缓存，这是数据库引擎查询的内容，目的是提高我们寻求的效率。

当从网页发出SQL语句（称为“临时语句”）时，引擎将查找现有的执行计划来处理该请求。因为这是从用户提交的文本，所以如果有效，将对其进行提取，解析，编译和执行。此时，它将收到零的查询费用。当数据库引擎使用其算法来确定从缓存中逐出哪些执行计划时，将使用查询成本。

默认情况下，临时查询的原始查询成本值为零。在随后由另一个用户进程（或相同的用户进程）执行完全相同的即席查询文本时，当前查询成本将重置为原始编译成本。由于我们的临时查询编译成本为零，因此对于重用的可能性并不是一个好兆头。显然，零是最小的整数，但是为什么要逐出它呢？

如果出现内存压力，并且如果您有一个经常使用的站点，则数据库引擎将使用清理算法来确定如何回收过程高速缓存正在使用的内存。它使用当前查询成本来决定驱逐哪些计划。您可能会猜到，成本为零的计划是第一个从缓存中撤出的计划，因为零本质上意味着“该计划的当前用户或对此计划的引用”。


注意：临时执行计划-当前成本会因每个用户流程以及计划的原始编译成本而增加。但是，任何计划的最大成本都不能超过其原始编译成本……在临时查询的情况下……为零。因此，它将被该值“增加” ...零-从本质上讲，它将保持最低的成本计划。

因此，很可能在以下情况下首先取消此类计划：出现记忆压力。

因此，如果您的服务器具有大量超出“您的需求”的内存，则您可能不会像只有“足够”内存来处理其工作负载的繁忙服务器那样经常遇到此问题。 （抱歉，服务器内存容量和利用率在某种程度上是主观的/相对的，尽管算法不是。）

现在，如果我对一个或多个要点确实不正确，我肯定会被纠正。

最后，作者写道：

“现在我们有了语句级的优化，因此来自应用程序的适当参数化的查询可以利用与之相同的执行计划。查询嵌入到存储过程中。”

我相信作者指的是“优化临时工作负载”选项。如果是这样，则此选项允许两个步骤过程，避免立即将完整的查询计划发送到过程高速缓存。它只在那里发送一个较小的查询存根。如果在查询存根仍在过程高速缓存中时将确切的查询调用发送回服务器，则完整的查询执行计划将被保存到过程高速缓存中。这样可以节省内存，在发生内存压力事件期间，驱逐算法可以比较大的查询计划更不频繁地驱逐存根。同样，这取决于服务器的内存和利用率。

但是，由于默认情况下处于关闭状态，因此必须将其打开。

最后，我想强调的是，开发人员经常将SQL嵌入到页面，组件和其他位置的原因是因为他们希望灵活并向数据库引擎提交动态SQL查询。 。因此，在实际的用例中，当向SQL Server提交临时查询时，不太可能提交相同的文本（即呼叫调用），也不会像我们寻求的缓存/效率那样。

有关其他信息，请参阅：

https://technet.microsoft.com/zh-cn/library/ms181055(v=sql.105).aspx
http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql

亨利