任何SQL Server多记录的存储过程陷阱？ [英] Any SQL Server multiple-recordset stored procedure gotchas?
问题描述
我现在的项目是一个大十岁上下的公共站点（每天200万的综合浏览量）网站运行ASP经典和asp.net的混合物与SQL Server 2005的后端。我们是沉重的读取，偶尔写，几乎没有更新/删除。我们的页面通常涉及到一个主对象与堆栈相关的（细节）的对象。
My current project is a large-ish public site (2 million pageviews per day) site running a mixture of asp classic and asp.net with a SQL Server 2005 back-end. We're heavy on reads, with occasional writes and virtually no updates/deletes. Our pages typically concern a single 'master' object with a stack of dependent (detail) objects.
我想返回所有在一个单一的PROC（绝对没有不必要的数据）所需的页面的数据的想法。诚然，这需要一个专门的PROC这样的网页，但一些网页获得我们的网站整体流量两位数的百分比，因此值得花时间/维护命中。我们通常只消耗多的记录，从我们的.NET code，使用System.Data.SqlClient.SqlDataReader和它的NextResult方法。哦，是的，我没有做这些特效任何更新/插入任何（除表变量）。
I like the idea of returning all the data required for a page in a single proc (and absolutely no unnecesary data). True, this requires a dedicated proc for such pages, but some pages receive double-digit percentages of our overall site traffic so it's worth the time/maintenance hit. We typically only consume multiple-recordsets from our .net code, using System.Data.SqlClient.SqlDataReader and it's NextResult method. Oh, yeah, I'm not doing any updates/inserts in these procs either (except to table variables).
SQL Server的（2005）特效而返回的多个记录集运作良好（在督促）对我们来说，到目前为止，但我有点担心，多记录集的特效是我的新宠锤子我打的每一个问题（钉）用。是否有任何多记录的SQL Server进程内陷阱我应该知道的？凡是将会使我希望我没有使用过他们吗？特别是有这样的事影响了连接池，内存利用率等。
SQL Server (2005) procs which return multiple recordsets are working well (in prod) for us so far but I am a little worried that multi-recordset procs are my new favourite hammer that i'm hitting every problem (nail) with. Are there any multi-recordset sql server proc gotchas I should know about? Anything that's going to make me wish I hadn't used them? Specifically anything about it affecting connection pooling, memory utilization etc.
推荐答案
下面的几个陷阱的多记录存储的特效：
Here's a few gotchas for multiple-recordset stored procs:
他们更难以重用code。如果你正在做的几个查询，赔率是你能够重用这些查询另一页上的一个。
They make it more difficult to reuse code. If you're doing several queries, odds are you'd be able to reuse one of those queries on another page.
他们更难以进行单元测试。每当你做出改变来查询的一个，你必须测试所有结果。如果有什么变化，你要挖通，看看哪些查询失败的单元测试。
They make it more difficult to unit test. Every time you make a change to one of the queries, you have to test all of the results. If something changed, you have to dig through to see which query failed the unit test.
他们更难以调整性能更高版本。如果另一个DBA来在你的身后，帮助业绩改善，他们必须做更多的切片和切块找出其中的问题的来源。然后，结合这与code再利用的问题 - 如果他们优化一个查询，该查询可能会在几个不同的存储的特效用了，然后他们必须去解决所有的人 - 这使得更多的单元测试再次
They make it more difficult to tune performance later. If another DBA comes in behind you to help performance improve, they have to do more slicing and dicing to figure out where the problems are coming from. Then, combine this with the code reuse problem - if they optimize one query, that query might be used in several different stored procs, and then they have to go fix all of them - which makes for more unit testing again.
他们做出错误处理更加困难。四，在存储过程中的查询可能成功，而第五个失败。你必须计划这一点。
They make error handling much more difficult. Four of the queries in the stored proc might succeed, and the fifth fails. You have to plan for that.
他们可以增加锁定问题和tempdb中产生的负荷。如果您存储的特效设计的方式，需要重复读取，那么你的东西变成一个存储过程的多个查询，时间越长，是怎么回事拿地运行，并且时间越长，要采取以回返回的结果为你的应用服务器。这增加的时间用来锁更高的争夺，而更多的SQL Server有存储在tempdb中的行版本。你刚才提到你是沉重的读取，所以这个具体问题应该不会太糟糕了你，但你希望你再用这种锤子在写密集型应用程序之前，要意识到这一点。
They can increase locking problems and incur load in TempDB. If your stored procs are designed in a way that need repeatable reads, then the more queries you stuff into a stored proc, the longer it's going to take to run, and the longer it's going to take to return those results back to your app server. That increased time means higher contention for locks, and the more SQL Server has to store in TempDB for row versioning. You mentioned that you're heavy on reads, so this particular issue shouldn't be too bad for you, but you want to be aware of it before you reuse this hammer on a write-intensive app.
这篇关于任何SQL Server多记录的存储过程陷阱？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
