使用 JDBC 遍历大表的最快方法 [英] Fastest way to iterate through large table using JDBC

问题描述

我正在尝试创建一个 Java 程序来清理和合并表中的行.该表很大，大约有 50 万行，我当前的解决方案运行速度非常慢.我想做的第一件事就是获取一个内存中的对象数组，代表我的表的所有行.这是我正在做的:

I'm trying to create a java program to cleanup and merge rows in my table. The table is large, about 500k rows and my current solution is running very slowly. The first thing I want to do is simply get an in-memory array of objects representing all the rows of my table. Here is what I'm doing:

• 一次选择 1000 行的增量
• 使用 JDBC 获取以下 SQL 查询的结果集SELECT * FROM TABLE WHERE ID > 0 AND ID <;1000
• 将结果数据添加到内存数组
• 以 1000 为增量继续查询直至 500,000，每次添加结果.

这需要很长时间.事实上，它甚至没有超过从 1000 到 2000 的第二个增量.查询需要永远完成(尽管当我直接通过 MySQL 浏览器运行相同的东西时，它的速度相当快).自从我直接使用 JDBC 以来已经有一段时间了.有没有更快的替代方案?

This is taking way to long. In fact its not even getting past the second increment from 1000 to 2000. The query takes forever to finish (although when I run the same thing directly through a MySQL browser its decently fast). Its been a while since I've used JDBC directly. Is there a faster alternative?

推荐答案

首先，你确定你需要内存中的整个表吗?也许您应该考虑(如果可能)选择要更新/合并/等的行.如果您真的必须拥有整个表格，您可以考虑使用可滚动的 ResultSet.你可以像这样创建它.

First of all, are you sure you need the whole table in memory? Maybe you should consider (if possible) selecting rows that you want to update/merge/etc. If you really have to have the whole table you could consider using a scrollable ResultSet. You can create it like this.

// make sure autocommit is off (postgres)
con.setAutoCommit(false);

Statement stmt = con.createStatement(
                   ResultSet.TYPE_SCROLL_INSENSITIVE, //or ResultSet.TYPE_FORWARD_ONLY
                   ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("select * from ...");

它使您可以使用绝对"和相对"方法移动到所需的任何行.

It enables you to move to any row you want by using 'absolute' and 'relative' methods.

这篇关于使用 JDBC 遍历大表的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！