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

杰夫阿特伍德在这里写到了这一点,虽然我理解存储过程可以提供的理论性能提升,但它看起来确实是一个巨大的痛苦.

您会看到哪些类型的查询使用存储过程可以提高性能,您更愿意直接构建哪些类型的查询？

任何文件都会以这种或那种方式受到高度赞赏.

1> Corbin March..：

---

存储的proc/no存储过程参数已成为一个宗教问题.对于每个强调优化的proc执行计划的人,另一个指出常见的动态查询在大多数现代DBMS中被缓存和优化.对于指出proc可能提供的安全性的任何人,另一个解释说动态查询可以做得同样安全.有些人喜欢在不重新编译应用程序的情况下更改proc的灵活性,而其他人认为应该在应用程序代码中捕获查询,以便它们在相同的代码库中生存和增长.

我说...

你喜欢什么.我怀疑我们能否拿出正确的答案.如果过程很麻烦,请不要使用它们.如果他们看起来是个好主意,那就去吧.我和两个模特一起工作过,老实说,我没有偏好.无论是否有他,我都很有成效.

---

好答案.但最好还是了解血腥细节,这样才能证明你选择的路线是正确的.有关这些细节,请参阅我的回答.

2> Marc Gravell..：

---

在过去的日子里,使用存储过程可以带来相当大的性能优势,但现在查询计划的重用性要好得多,因此在很多情况下两者几乎相同.

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

使用存储过程有一些好处:

他们确切地解决了db会发生什么

对于严格的安全方案,您可以严格控制对存储过程(而不是表)的访问

如果计划在点上变得怪异,你可以细分查询(嗅探)

但是,SQL也有优点:

它允许ORM工具(例如LINQ)动态编写可组合查询(第4页,由Foo,Bar订购)

它允许您用更具表现力的语言编写动态代码(TSQL实际上并不打算用于编写TSQL!)

你通常有更好的工具

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

3> RoadWarrior..：

---

与软件一样,SQL缓存问题比它看起来更微妙.例如,让我们看一下ad-hoc 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使计划无效.

4> Andrew..：

---

我们发现存储过程的一个好处是,一旦最初由开发人员创建,它们可以移交给DBA或数据库调优专家,以便在出于性能原因需要时更好地编写.

DBA显然不需要访问应用程序代码来执行此操作,并且可以与待处理的应用程序版本一起使用.

当然,内联或嵌入式SQL查询也是如此,但我认为存储过程更适合这种合作,更大的团队工作方式.

---

Persoanlly,我希望我的"调优专家"成为了解应用程序代码的常规开发人员.特别是,我希望他们可以直接访问相同的集成测试(可能在C#等).此外,很多时候,重构应用程序代码本身是提高性能的最佳方法.

5> paxdiablo..：

---

在我看来,存储过程的最大优点是:

1 /它们集中在数据库管理系统中,因此DBA确切知道他们正在做什么,并可以优化数据库以适应它们.来自客户的即席查询要难得多.我已经看到数据库被买了,因为DBA允许不受约束的查询.他们还迫使客户思考他们需要什么.

2 /他们可以在服务器上进行非常复杂的处理,从而最大限度地减少通过线路发送的数据量.这样可以避免应用程序获取大量数据,从中确定数据,然后获取更多数据等等.

无论如何,在开发期间允许任何随机查询,但在进入生产之前,确定应将哪些数据库操作移动到存储过程.

作为兼职DBA,我认为这意味着所有这些,但只要您对应用程序可以发送的查询保持一点控制,就没有必要.