如何找出选择查询的最佳提取大小 [英] 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.

如何找到最佳尺寸?这对数据库方面有影响吗，它会占用大量内存吗?

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的访存大小，这实际上是一个合理的最佳设置.那时值之间的性能差异通常很小，您可以期望运行之间的大多数性能差异是正常随机变化的结果，而不是由访存大小的变化引起的.如果您要获得特定配置中特定工作负载的最后性能指标，则可以肯定地进行分析.不过，对于大多数人来说，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屋！