Java - DB2 性能改进
我们有一个 SELECT 语句，大约需要花费时间。 3秒执行。我们在嵌套的 While 循环内调用此 DB2 查询。
例如：
虽然(hashmap1.hasNext()){ while(hashmap2.hasNext()){ SQL查询 } 问题
是，外部 While 循环将执行大约。 1200 次，内部 While 循环将执行 200 次。这意味着 SQL 将被调用 1200*200 = 240,000 次。大约。外部 While 循环的每次迭代将花费 150 秒。因此，1200 * 150 秒 = 50 小时。
在开始下一个流程之前，我们只能花费大约 12-15 小时的时间。
有什么办法可以快速完成这个过程吗？任何可以帮助我们更快地从 DB2 获取这些记录的新技术。
任何帮助将不胜感激。
注意：我们已经研究了减少迭代次数的所有可能方法。
We have a SELECT statement which will take approx. 3 secs to execute. We are calling this DB2 query inside a nested While loop.
Ex:
While(hashmap1.hasNext()){
while(hashmap2.hasNext()){
SQL Query
}
}
Problem is, the outer While loop will execute approx. 1200 times and inner While loop will execute 200 times. Which means the SQL will be called 1200*200 = 240,000 times. Approx. each iteration of Outer While loop will take 150 secs. So, 1200 * 150 secs = 50 hrs.
We can afford only around 12-15hrs of time, before we kick off the next process.
Is there any way to do this process quickly? Any new technology which can help us in fetching these records faster from DB2.
Any help would be highly appreciated.
Note: We already looked into all possible ways to cut down the no.of iterations.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在我看来，您正在尝试使用中间层来实现数据库本身更适合的功能。这是一个经典的“N+1”查询问题。
我将重写此逻辑，以将其作为正确索引的 JOIN 完全在数据库上执行。这不仅会减少所有来回的网络，而且会让数据库优化器承担并节省您将所有数据带到中间层进行处理的费用。
Sounds to me like you're trying to use the middle tier for something that the database itself is better suited for. It's a classic "N+1" query problem.
I'd rewrite this logic to execute entirely on the database as a properly indexed JOIN. That'll not only cut down on all that network back and forth, but it'll bring the database optimizer to bear and save you the expense of bringing all that data to the middle tier for processing.