存储过程真的能提高MS / .NET的性能吗？

Question

Jeff Atwood写了关于这个here的文章，虽然我理解存储过程可以提供的理论上的性能提升，但它看起来确实是一个巨大的痛苦。

使用存储过程，您会看到哪些类型的查询性能提升最多，您更愿意动态构建哪些类型的查询？

以这样或那样的方式编写任何文档都是非常感谢的。

Answer 1

stored proc/no stored proc参数已成为宗教问题。对于每个强调优化procs执行计划的人来说，另一个人指出，在大多数现代DBMSes中，常见的动态查询都被缓存和优化。对于任何指出proc可能提供的安全性的人来说，另一个人解释说，动态查询也可以同样安全。一些人喜欢无需重新编译应用程序就可以更改proc的灵活性，而另一些人则认为查询应该在应用程序代码中捕获，以便它们在相同的代码库中生存和增长。

我说..。

做你喜欢的事。我怀疑我们能否得出正确的答案。如果procs很麻烦，那就不要使用它们。如果它们看起来是个好主意，那就去做吧。这两个模型我都用过，老实说，我没有偏爱。不管有没有他们我都很有效率。

Answer 2

在过去，使用存储过程会带来相当大的性能优势，但是查询计划重用现在要好得多，以至于在许多情况下两者几乎是一样的。

如果要在服务器上构建动态SQL，则可以通过使用sp_ExecuteSQL (而不仅仅是EXEC)来执行SQL，从而进一步提高查询计划的重用(和注入安全性)。

使用存储过程有一些优点：

• 它们精确地解决了db
• 将发生的问题对于严格的安全方案，您可以严格控制对存储过程(而不是表)的访问
• 如果计划变得异常(嗅探)

，您可以细分查询。

但是，SQL也有一些优点：

• 它允许对象关系管理工具(例如LINQ)动态地编写可组合的查询(第4页，按Foo和Bar排序)
• 它允许您用更具表现力的语言编写动态代码(TSQL实际上并不是用来编写TSQL的！)
• 您通常有更好的工具

我肯定会使用SP的一些事情是在(例如)数据迁移步骤中-例如，对于批量操作，我可能会使用SqlBulkCopy将数据推送到临时表中，然后使用存储过程移动数据。除此之外，我是相当灵活的。

Answer 3

与软件一样，SQL缓存问题比看起来更微妙。例如，让我们看一下即席SQL查询的缓存：

-- First, clear the cache
DBCC FREEPROCCACHE

-- Look at what executable plans are in cache
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'

-- Execute the following statement
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE t.price = 19.99

-- Look at what executable plans are in cache and you'll 
-- find that there's a plan for a NUMERIC(4,2)
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'

-- If you execute the EXACT same statement with a 4,2 
-- then you will get THAT plan. But if you execute with a 5,2
-- then you'll get a new plan. Try this:
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE price = 199.99

-- Look again at the cached executable plans, and you'll see a NEW one...
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'

但是，您可以使用sp_executesql键入参数并强制缓存计划。所有后续的使用都将得到相同的计划，但有些人不喜欢这种方法的晦涩：

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = N'SELECT t.* FROM dbo.titles AS t WHERE t.price = @price'
EXEC sp_executesql @ExecStr, N'@price money', 19.99

现在，如果您创建一个类似于带有价格参数的存储过程的查询，则计划将在第一次执行时被创建并缓存在内存(而不是磁盘)中，并且无论该参数的值如何都会被重用。

存储过程计划缓存在内存中，而不是在磁盘上，这意味着它将在服务器重新启动时或由于重用较低而从缓存中消失。如果该过程所依赖的数据发生了足够多的更改，从而导致统计信息失效，那么它也可能会从缓存中掉出来。这会导致SQL Server使计划无效。