blocks|key|2671279|text|您正在尝试通过执行以下操作来一次性获取10M条记录。这绝对会像任何东西一样吞噬你的记忆|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2671280|String+query+=+"select+*+from+"+%2B+originTable;
ResultSet+rs+=+originDBOperate.executeQuery(query);//get+origin+table|code-block|syntax|javascript|2671281|使用分页查询读取批处理并进行相应的批处理更新。|2671282|您甚至不是在进行批处理更新，而是通过执行以下代码一个接一个地触发10K查询|2671283|String+insertSql+=+buildInsertSql(base,rs,cols);//corresponding+insert+sql
++++targetDBOperate.executeSql(insertSql);
++++if(count%2510000==0)+{
++++++++targetDBOperate.commit();//+This+simply+means+that+you+are+commiting+after+10K+records
++++}|2671284|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You are trying to fetch 10M records in one go by doing the following. This will definitely eat your memory like anything

<pre><code>String query = "select * from " + originTable;
ResultSet rs = originDBOperate.executeQuery(query);//get origin table
</code></pre>

Use paginated queries to read the batches and do batch updates according.

You are not even doing a batch-update You are simply firing 10K queries one after the other by doing the following code

<pre><code>String insertSql = buildInsertSql(base,rs,cols);//corresponding insert sql
 targetDBOperate.executeSql(insertSql);
 if(count%10000==0) {
 targetDBOperate.commit();// This simply means that you are commiting after 10K records
 }
</code></pre>

blocks|key|15143|text|通过在、SQLite、或任何其他关系数据库中移动更多的行，您应该遵循一些基本原则：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|15144|1)将autoCommit设置为false，即不提交每次插入|CODE|15145|2)使用批量更新，即每行不往返|15146|3)使用预准备语句，即不要解析每个insert。|15147|将这些代码放在一起，就会得到以下代码：|15148|cn是源连接，cn2是目标连接。|15149|对于每个插入的行，您调用addBatch，但对于每个batchSize，您只调用executeBatch一次，这将启动一个往返过程。|15150|不要忘记循环末尾的最后一个executeBatch和最后一个commit。|15151|cn2.setAutoCommit(false)

String+SEL_STMT+=+"select+id,+col1,col2+from+tab1"
String+INS_STMT+=+"insert+into+tab2(id,+col1,col2)+values(?,?,?)"

def+batchSize+=+10000


def+stmt+=+cn.prepareStatement(SEL_STMT)
def+stmtIns+=+cn2.prepareStatement(INS_STMT)+

rs+=+stmt.executeQuery()

while(rs.next())
++{
++++stmtIns.setLong(1,rs.getLong(1))
++++stmtIns.setString(2,rs.getString(2))
++++stmtIns.setTimestamp(3,rs.getTimestamp(3))
++++stmtIns.addBatch();
++++i+%2B=+1

++++if+(i+==+batchSize)+{
++++++++def+insRec+=+stmtIns.executeBatch();
++++++++i+=+0
++++++++}

++}
rs.close()
stmt.close()

def+insRec+=+stmtIns.executeBatch();

stmtIns.close()
cn2.commit()|code-block|syntax|javascript|15152|使用sqlite-jdbc-3.23.1按照您的大小进行示例测试：|15153|inserted+rows:+10000000
total+time+taken+to+insert+the+batch+=+46848+ms|15154|我没有观察到任何内存问题或大型事务的问题|15155|entityMap^0|3|8|0|3|A|G|5|0|4|4|0|4|5|0|0|0|2|7|3|0|C|8|Q|9|14|C|0|D|C|U|6|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1A|8|@$9|1B|A|1C|B|H]|$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|I|3|J|5|6|7|1F|8|@$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|K|3|L|5|6|7|1I|8|@$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|1L|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|1M|8|@$9|1N|A|1O|B|H]|$9|1P|A|1Q|B|H]]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1R|8|@$9|1S|A|1T|B|H]|$9|1U|A|1V|B|H]|$9|1W|A|1X|B|H]]|D|@]|E|$]]|$1|S|3|T|5|6|7|1Y|8|@$9|1Z|A|20|B|H]|$9|21|A|22|B|H]]|D|@]|E|$]]|$1|U|3|V|5|W|7|23|8|@]|D|@]|E|$X|Y]]|$1|Z|3|10|5|6|7|24|8|@]|D|@]|E|$]]|$1|11|3|12|5|W|7|25|8|@]|D|@]|E|$X|Y]]|$1|13|3|14|5|6|7|26|8|@]|D|@]|E|$]]|$1|15|3|-4|5|6|7|27|8|@]|D|@]|E|$]]]|16|$]]

By moving a higher number of rows in SQLite or any other relational database you should follow some basic principles:

1) set <code>autoCommit</code> to false, i.e. do not commit each insert

2) use batch update, i.e. do not round trip for each row

3) use prepared statement, i.e. do not parse each insert.

Putting this together you get following code:

<code>cn</code> is the source connection, <code>cn2</code> is the target connection.

For each inserted row you call <code>addBatch</code>, but only once per <code>batchSize</code> you call <code>executeBatch</code> which initiates a round trip.

Do not forget a last <code>executeBatch</code> at the end of the loop and the final <code>commit</code>.

<pre><code>cn2.setAutoCommit(false)

String SEL_STMT = "select id, col1,col2 from tab1"
String INS_STMT = "insert into tab2(id, col1,col2) values(?,?,?)"

def batchSize = 10000


def stmt = cn.prepareStatement(SEL_STMT)
def stmtIns = cn2.prepareStatement(INS_STMT) 

rs = stmt.executeQuery()

while(rs.next())
 {
 stmtIns.setLong(1,rs.getLong(1))
 stmtIns.setString(2,rs.getString(2))
 stmtIns.setTimestamp(3,rs.getTimestamp(3))
 stmtIns.addBatch();
 i += 1

 if (i == batchSize) {
 def insRec = stmtIns.executeBatch();
 i = 0
 }

 }
rs.close()
stmt.close()

def insRec = stmtIns.executeBatch();

stmtIns.close()
cn2.commit()
</code></pre>

Sample test with your size with sqlite-jdbc-3.23.1:

<pre><code>inserted rows: 10000000
total time taken to insert the batch = 46848 ms
</code></pre>

I do not observe any memory issues or problems with a large transaction

I want to copy a table (10 million records) in originDB(sqlite3) into another database called targetDB. 
The process of my method is:
read data from the origin table and generate a ResultSet, then generate corresponding insert sql about every record and execute commit to batch insert when the count of record reach 10000. The code as follow: 

<pre><code>public void transfer() throws IOException, SQLException {
 targetDBOperate.setCommit(false);//batch insert
 int count = 0;
 String[] cols = parser(propertyPath);//get fields of data table
 String query = "select * from " + originTable;
 ResultSet rs = originDBOperate.executeQuery(query);//get origin table
 String base = "insert into " + targetTable;
 while(rs.next()) {
 count++;
 String insertSql = buildInsertSql(base,rs,cols);//corresponding insert sql
 targetDBOperate.executeSql(insertSql);
 if(count%10000==0) {
 targetDBOperate.commit();// batch insert
 }
 }
 targetDBOperate.closeConnection();
}
</code></pre>

The follow picture is the trend of using memory, and vertical axis represents memory usage 
<a href="https://i.stack.imgur.com/8Lidq.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8Lidq.jpg" alt="enter image description here"></a>

As we can say it will be bigger and bigger until out of memory. The stackoverflow has some relevant question such as <a href="https://stackoverflow.com/questions/45975298/out-of-memory-when-inserting-records-in-sqlite-firedac-delphi">Out of memory when inserting records in SQLite, FireDac, Delphi
</a>, but I havent solve my problem for we use different implement method. My hypothesis is that when the count of record hasn't reach 10000, these corresponding insert sql will be cached in memory and they haven't been removed when execute commit by default? Every advice will be appreciate.

out of memory when insert record batch through jdbc

数据库

SQLite

我想将originDB(sqlite3)中的一个表(1000万条记录)复制到另一个名为targetDB的数据库中。我的方法的过程是:从原始表中读取数据，生成一个ResultSet，然后为每条记录生成对应的insert sql，当记录数达到10000条时，执行commit进行批量插入。代码如下：public void t...

问通过jdbc插入记录批次时内存不足
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过jdbc插入记录批次时内存不足EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过jdbc插入记录批次时内存不足
EN