blocks|key|1284302|text|第二种选择肯定会更快，因为它是单个网络往返，以及单个SP呼叫。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1284303|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The second option will certainly be faster because it is a single network round trip, as well as a single SP call.

blocks|key|5160888|text|确切地说是第二个，从大约10倍到更快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5160889|如果您对id所做的任何操作都可以在set操作中完成，那么与单独调用SP相比，您将获得数倍的性能提升。|5160890|我经常有类似这样的proc：|5160891|5160892|+create+procedure+proc+(+@ids+varchar(max)+)+as+select+*+from+users_tbl+u内部联接spiltCSV(@ids)c+on+c.id+=+u.id+--等等|blockquote|5160893|5160894|5160895|这是一个基于集合的操作；与在proc中使用游标或使用for循环迭代调用具有单个id的过程的过程方法不同。|5160896|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|T|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|V|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|W|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|O|$]]

Definetly the second, varying from about 10x faster to a little faster.

If whatever you're doing with the id's can be done in a set operation, you'll get several times the performance gain than calling the SP individually.

I regularly have procs that look like:

<blockquote>
<pre><code>create procedure proc ( @ids varchar(max) ) as
select * from users_tbl u
inner join spiltCSVs(@ids) c
 on c.id = u.id
--so on and so forth
</code></pre>
</blockquote>

That's a set based operation; as opposed to the procedural method using a cursor in the proc, or using the for loop to iterate over calling the procedure with an individual id.

blocks|key|3693531|text|因为这不适用于ocdecio的答案...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3693532|只是扩展一下..。在我所见过的大多数系统中，网络流量是性能的限制因素(假设数据库和前端代码经过合理的调优，这并不是绝对可怕的)。即使您的web服务器和数据库服务器在同一台计算机上，如果您频繁地在两者之间来回调用，进程间通信也可能是一个限制因素。|3693533|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Since this wouldn't fit in a comment for ocdecio's answer...

Just to expand on it... in most systems that I've seen the network traffic is the limiting factor for performance (assuming a reasonably tuned database and front-end code that isn't absolutely horrible). Even if your web server and database server are on the same machine, the interprocess communication can be a limiting factor if you have frequent calls back and forth between the two.

blocks|key|1377038|text|在每次页面加载时，还是在第一次加载页面时？我不想为ASP.NET页面中的每一个回发都这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1377039|为了更直接地回答您的问题，如果您能够通过发送多个ID来获得多个记录，请这样做。如果您需要10个以上的项目，则效率更高，可伸缩性更强。|1377040|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

On each page load, or the first time the page is loaded? I would not want to do that for every single postback in an ASP.NET page.

To answer your question more directly, if you're able to get multiple records by sending multiple IDs, do that. More efficient and more scalable should you ever need more than 10 items.

blocks|key|5160859|text|这完全取决于proc是如何编码的，如果您在第二个proc中传入了10项，然后该proc使用游标来获取这些行，那么第一次调用可能会更快|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5160860|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

it all depends how the proc is coded, if you pass in 10 items in the 2nd proc and that proc then uses a cursor to get those rows then the first call might be faster

blocks|key|1377178|text|第二步的速度会有多快取决于太多的东西。与结果集的大小相比，网络开销可能微不足道。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1377179|还有另一种选择(根据锁定行为，它应该比这两种选择都要快)，那就是call+all+of+them+asynchronously+-那么当最长的一个完成时，您的页面就可以有效地完成。显然，这将需要一些额外的编码。|offset|length|1377180|在此示例中，只有一个SP开销。我们假设SP返回客户端将拆分/处理的单个行集或多个行集：|1377181|int[]+ids;+//+array+of+ids
CallSQLStoredProc(ids)++//+stored+procedure+returns+more+than+one+row+for+each+id|code-block|syntax|javascript|1377182|在此示例中，SP调用开销是单个调用的n倍。并且调用是序列化的：|1377183|foreach(item+in+mylist)+{
++++CallSQLStoredProc(item.id);
}|1377184|在第三种选择中：|1377185|foreach(item+in+mylist)+{
++++StartSQLStoredProc(item.id);
}

//+Continue+building+the+page+until+you+reach+a+point+where+you+absolutely+have+to+have+the+data

wait();|1377186|这仍然有n个数据库调用开销，但性能提升可能取决于SQL+Server和网络的容量，以便并行化工作负载。此外，您还可以在构建页面时启动SQL+Server工作。|1377187|单SP解决方案仍然可以取胜，特别是如果它可以将单个结果集与SQL+Server可以并行化任务的UNION组合在一起。但是，如果结果集具有单独的模式，或者UNION不能很好地执行，则多SP异步解决方案可以击败它(并且还可以利用在页面中执行其他工作的能力)。|1377188|entityMap|0|LINK|mutability|MUTABLE|url|http://www.devx.com/dotnet/Article/26747^0|0|W|V|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@]|9|@$D|18|E|19|1|1A]]|A|$]]|$1|F|3|G|5|6|7|1B|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|1C|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1D|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|1E|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1F|8|@]|9|@]|A|$]]|$1|S|3|T|5|J|7|1G|8|@]|9|@]|A|$K|L]]|$1|U|3|V|5|6|7|1H|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]]]

How much faster the second will be really depends on too many things. The network overhead might be insignificant compared to the size of your result sets.

There is another alternative (which should be faster than either depending on the locking behavior), which is to <a href="http://www.devx.com/dotnet/Article/26747" rel="nofollow noreferrer">call all of them asynchronously</a> - then your page can effectively complete when the longest one completes. Obviously, this will require some additional coding.

In this example, there is only one SP overhead. We'll assume the SP returns either a single rowset which the client will split/process or multiple rowsets:

<pre><code>int[] ids; // array of ids
CallSQLStoredProc(ids) // stored procedure returns more than one row for each id
</code></pre>

In this example, the SP call overheads are n times the single call. and the calls are serialized:

<pre><code>foreach(item in mylist) {
 CallSQLStoredProc(item.id);
}
</code></pre>

In the third alternative:

<pre><code>foreach(item in mylist) {
 StartSQLStoredProc(item.id);
}

// Continue building the page until you reach a point where you absolutely have to have the data

wait();
</code></pre>

This still has the n DB call overheads, but the performance improvement can depend on the capacity of the SQL Server and network in order to parallelize the workload. In addition you get the benefit of the ability to start the SQL Server working while the page is building.

The single SP solution can still win out, particularly if it can assemble a single result set with a UNION where the SQL Server can parallelize the task. However, if the result sets have separate schemas or the UNION cannot perform well, A multiple SP asynchronous solution can beat it out (and can also take advantage of the ability to do other work in the page).

blocks|key|3693608|text|迭代任何东西都会导致更多的开销。迭代提高性能的情况并不多见。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3693609|我的建议一直是在编程中避免两件事：|3693610|3693611|if+then+else+statments|ordered-list-item|3693612|iteration|3693613|3693614|您总是会遇到这两种情况，但使用的越少，您的应用程序就越有可能运行得更快、更流畅。|3693615|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|S|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

Iterating anything is going to always cause more overhead. There aren't many situations where iteration improves performance.

My advice has always been to avoid 2 things in programming:

<ol>
<li>if then else statments</li>
<li>iteration</li>
</ol>

You will always have situations where you will use both, but the less you use of them the more potential your application has to run faster and smoother.

blocks|key|5161101|text|如果您希望在应用程序中实现可伸缩性，则需要尽可能多地使用缓存。任何共享查询都应该只运行一次，并将结果存储在缓存中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5161102|至于您的查询，如果您没有在每个ID的查询中使用游标，那么如果网络延迟对您所做的事情有重要影响，那么它应该会更快。当有疑问的时候，测量一下。当我在我的函数上实现计时，看看不同的事情花了多长时间时，我很多次都感到非常惊讶。|5161103|在.net中，System.Diagnostics.StopWatch是您的朋友:)。|5161104|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

IF you want scalability in your application, you will want to use caching as much as possible. You should be running any shared queries only once and storing the result in the cache.

As for your query, provided you aren't using cursors in the query for each ID, it should be faster provided that network latency has meaningful impact on what your doing. When in doubt, measure. I've been very surprised many times when I actually implemented timing on my functions to see how long different things took.

In .net System.Diagnostics.StopWatch is your friend :).

I have a question regarding the performance of SQL. I will illustrate my problem with pseudocode.

I am wondering which will preform better and by how much? Say for 10 items, on each page load. In .NET. Is is a lot faster? a little faster? A difference not noticable on SQL?

<pre><code>foreach(item in mylist) {
CallSQLStoredProc(item.id);
}
</code></pre>

vs

<pre><code>int[] ids; // array of ids
CallSQLStoredProc(ids) // stored procedure returns more than one row for each id
</code></pre>

SQL Performance Question

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我有一个关于SQL性能的问题。我将用伪代码来说明我的问题。我想知道哪种预成型更好，效果如何？例如，每个页面加载10个项目。在.NET中。是不是快了很多？快一点吗？在SQL上有什么不明显的区别？foreach(item in mylist) {CallSQLStoredProc(item.id);}vsint[] ids; // array of idsCallSQLStoredProc(ids) 

问SQL性能问题
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL性能问题EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL性能问题
EN