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

我说。。。

做你喜欢做的事。我怀疑我们能想出正确的答案。如果程序很麻烦,不要使用它们。如果它们看起来是个好主意,那就去做吧。我和这两个模特都合作过,老实说,我没有偏好。不管有没有他们,我都很有效率。

相当大的 使用存储过程有助于提高性能,但查询计划的重用性现在显著提高,因此在许多情况下这两者几乎相同。

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

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

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

但是,SQL也有一些优点:

• 它允许ORM工具(例如LINQ)动态编写可组合查询(第4页,由Foo、Bar排序)
• 它允许您使用更具表现力的语言编写动态代码(TSQL并不是真正用于编写TSQL!)
• 您通常有更好的工具

除此之外,我相当灵活。

即席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使计划无效。

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

DBA显然不需要访问应用程序代码来执行此操作,并且可以与挂起的应用程序版本一起工作。

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

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

无论如何,在开发过程中允许任何随机查询,但在进入生产之前,请确定哪些DB操作应该移动到存储过程中。

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

提供性能优势的存储过程是SQL Server早期版本遗留下来的神话。SQL Server>版本7对所有查询都一视同仁,并将缓存所有常用查询的执行计划,无论其来源如何。

我想知道这个神话还会流传多少年。

caused a stir 刚才谈到这个话题。

在SQL Server上,更重要的是要理解任何性能的提高都是由于缓存了查询计划。查询计划的编译成本非常高,因此缓存它们是有意义的。编辑:临时SQL不太可能与缓存的查询计划匹配。。如果通过调用sp_executesql运行动态SQL,则可以缓存它。

存储过程背后的一个想法是,它允许您在访问数据库的一次过程中执行多项操作。

MSSQL允许在单个请求中提交查询批次;这大致相当。

但是,原则是一样的。在大型分布式系统中,数据库延迟(而不是查询在服务器上花费的时间)可能是一个性能问题。发出更少的查询可能会有好处。

更快的存储过程背后的想法是,它们可以存储缓存的查询计划,有点像预编译存储过程。因此,如果这个过程非常复杂,在很多表之间有很多连接,那么您可能会看到速度优势。

写得不好的存储过程不会提高性能。与错误的内联查询相同,错误的内联查询的性能将比经过深思熟虑的查询差。

人们忽略的一件事是,所有这些CRUD存储过程都充满了coalesce和if语句,这些语句非常慢。