如何确定选择查询的最佳提取大小 [英] How to figure out the optimal fetch size for the select query

问题描述

在 JDBC 中，默认提取大小为 10，但我想当我有一百万行时，这不是最佳提取大小.我知道提取大小太低会降低性能，但如果提取大小太高也会降低性能.

In JDBC the default fetch size is 10, but I guess that's not the best fetch size when I have a million rows. I understand that a fetch size too low reduces performance, but also if the fetch size is too high.

如何找到最佳尺寸?而且这对DB端有影响吗，会不会占用大量内存?

How can I find the optimal size? And does this have an impact on the DB side, does it chew up a lot of memory?

推荐答案

与(几乎)任何事情一样，为特定参数找到最佳大小的方法是使用不同的值对您尝试优化的工作负载进行基准测试参数.在这种情况下，您需要使用不同的提取大小设置运行代码，评估结果并选择最佳设置.

As with (almost) anything, the way to find the optimal size for a particular parameter is to benchmark the workload you're trying to optimize with different values of the parameter. In this case, you'd need to run your code with different fetch size settings, evaluate the results, and pick the optimal setting.

在绝大多数情况下，人们选择 100 或 1000 的 fetch 大小，结果证明这是一个合理的最佳设置.此时值之间的性能差异通常非常小——您会认为运行之间的大部分性能差异是正常随机变化的结果，而不是由获取大小的变化引起的.如果您试图在特定配置中获得特定工作负载的最后一点性能，您当然可以进行该分析.不过，对于大多数人来说，100 或 1000 就足够了.

In the vast majority of cases, people pick a fetch size of 100 or 1000 and that turns out to be a reasonably optimal setting. The performance difference among values at that point are generally pretty minimal-- you would expect that most of the performance difference between runs was the result of normal random variation rather than being caused by changes in the fetch size. If you're trying to get the last iota of performance for a particular workload in a particular configuration, you can certainly do that analysis. For most folks, though, 100 or 1000 is good enough.

这篇关于如何确定选择查询的最佳提取大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！