英文:
Understanding Specifics of What ReadAsync is doing in ADO.NEt
问题
Here's the translation of the code portion:
考虑以下使用 ADO.NET 的代码：
var sw = Stopwatch.StartNew();
using (var reader = await command.ExecuteReaderAsync())
{
// 此时 sw 已经经过了 10 秒
while (await reader.ReadAsync())
{
}
// 此时 sw 已经经过了 15 秒
}
If you have any further questions or need translations of specific parts, please let me know.
英文:
Consider the following code using ADO.NET:
var sw = Stopwatch.StartNew();
using (var reader = await command.ExecuteReaderAsync())
{
// at this point sw has elapsed for 10s
while (await reader.ReadAsync())
{
}
// at this point sw has elapsed for 15s
}
I understand that the 10s spent in ExecuteReaderAsync was time running the query.
What exactly is the 5s spent reading the results?
- Is it only time sending the data over the network?
- Is any of that impacted by the query (like a poor query taking longer to stream results)?
- What could I do to speed up the 5s spent reading? Improve network speed, or something else?
I am trying to understand at a fairly deep level what all impacts the time spent reading.
Thanks in advance!
答案1
得分: 1
ExecuteReaderAsync方法等待的时间主要包括以下几个方面：
- 发送请求到服务器的时间（通常非常短，但不稳定的连接和长的响应时间可能会影响它）。
- 服务器编译查询的时间（如果已经缓存，几乎可以忽略）。
- 执行并获取第一个结果的时间。这里情况比较复杂：
- 如果查询的执行计划包含阻塞操作符，比如排序或哈希，那么第一个结果通常会花费较长时间。
- 如果执行计划没有阻塞操作符，那么第一个结果通常会很快返回。但是某些类型的查询可能会表现得更差，比如大型分析型查询。
- 这只是一个经验法则：实际性能会受到许多因素的影响。
- 这里的主要问题，无论是否有阻塞操作符，取决于查询计划是否良好。 您需要分析它并进行必要的更改。如果表正确索引并且查询编写良好，大多数小查询应在毫秒级返回。
- 服务器响应第一个结果的时间（通常也很小，但受到响应时间的影响）。
执行 reader.ReadAsync() 方法的时间纯粹是获取下一行的时间，对于阻塞计划或小型查询可能很快，对于大型非阻塞查询可能会很慢。网络速度也可能起到一定作用，尤其是处理非常大的数据块时。最终，读取大量数据需要很多时间。
正如评论中所指出的，ConfigureAwait(false) 也可以加速上下文切换，尽管这对数据库调用的影响可能不会很大。
英文:
The time waited at ExecuteReaderAsync is mainly for the following:
- Time to send the request to the server (usually very small, but bad connections and long ping times can affect it).
- Time for the server to compile it (almost nothing if it's already cached).
- Time to execute and get the first result. Here is where it's complicated:
- If the execution plan of the query contains a blocking operator, such as a Sort or Hash, then the first result can often take a long time.
- If the plan has no blocking operators then the first result can often come back very quickly. But certain types of queries can be worse off, such as large analytical queries.
- This is purely a rule of thumb: actual performance will depend on many things.
- The primary issue here, whether or not you have a blocking operator, is whether the query plan is good or not. You need to analyze it and change as necessary. Most small queries should return in milliseconds if the tables are correctly indexed and the query is written well.
- Time for the server to respond with the first result (also usually small but subject to ping times).
The time to execute reader.ReadAsync() is purely the time taken to fetch the next row, which could be fast for a blocking plan or a small query, and slow for a big non-blocking one. Network speed may play a factor also, as can very large blobs of data. Ultimately reading a lot of data takes a lot of time.
As noted in the comments, ConfigureAwait(false) can also speed up context switches, although this is unlikely to make a huge difference on a database call.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论