How do I increase performance on data load for large text file [duplicate]

Question

I am trying to insert a large text file into oracle database, my current program works but it is loading very slow. The text file is around 400 MB

What I did was like below...

... ArrayList<String> nta = new ArrayList<String>(); while ((line = br.readLine()) != null) { //add data position for each line read data.add(line.substring(0, 6)+ "="+line.substring(6, 24)+ "="+line.substring(24, 30)+ "="+line.substring(30, 48)); } db.insertRecord(data); ... public void insertRecord(ArrayList<String> data) { String sql = "Insert into Account (NAME, ID, RCBS, CA_NUM, GUID, PARN_GUID)"+ " values " "(?,?,?,?,?,?)"; ... ps = con.prepareStatements(sql); for(int i=0; i<data.size(); i++) { String[] fields = data.get(i).split("="); ps.setString(1, fields[0]); ps.setString(2, fields[1]); ps.setString(3, fields[2]); ps.setString(4, fields[3]); ps.setString(5, fields[0].trim()+"."+fields[1].trim()+"."+fields[2].trim()+"."+fields[3].trim()); //Index ps.setString(6, fields[0].trim()+"."+fields[1].trim()); //PARN Index ps.execute(); } //end loop con.commit(); ...

Are there any performance can be done to increase the speed of the data load?

Please stop and do it right way. For example by using SQL*Loader or external table. — Lukasz Szozda
– Lukasz Szozda, Commented Oct 27, 2017 at 14:18
The problem I am having is that there is a weird ascii character in the text file. when i use SQL loader it would load it as <?> in the table column — logger
– logger, Commented Oct 27, 2017 at 14:21
Use @CrazySabbath 's approach, I had a similar issue. I broke into "chunks" - I actually built a asynchronous api (that would utilize multiple threads) to handle our document processing - a little overkill but the learning process was great haha — Tez Wingfield
– Tez Wingfield, Commented Oct 27, 2017 at 14:24
Hi - what happens to the "weird ascii character" when inserted via java? I'd expect that you can speed this up by specifying the characterset in SQLLDR eg LOAD DATA CHARACTERSET MSWIN1252 — Niall Litchfield
– Niall Litchfield, Commented Oct 27, 2017 at 15:19

CrazySabbath · Accepted Answer · 2017-10-27 14:41:45Z

As suggested, you should use batch inserts when dealing with that many records. Note that this piece of code will execute inserts once every 1000 (not all at once) to avoid possible memory problems, batchInterval.

int batchInterval = 1000; ps = con.prepareStatements(sql); for(int i=0; i<data.size(); i++) { String[] fields = data.get(i).split("="); ps.setString(1, fields[0]); ps.setString(2, fields[1]); ps.setString(3, fields[2]); ps.setString(4, fields[3]); ps.setString(5, fields[0].trim()+"."+fields[1].trim()+"."+fields[2].trim()+"."+fields[3].trim()); //Index ps.setString(6, fields[0].trim()+"."+fields[1].trim()); //PARN Index ps.addBatch(); if (i % batchInterval == 0) ps.executeBatch(); } ps.executeBatch(); ps.close(); con.close();

You can find more here: batch inserts tutorial

Needle file · Accepted Answer · 2017-10-27 14:16:24Z

-1

send it to server as CLOB (many-many row in one portion) and parse on server side..

answered Oct 27, 2017 at 14:16

Needle file

5183 silver badges7 bronze badges

2 Comments

CrazySabbath Over a year ago

Are you suggesting him to parse 400MB size CLOBs on oracle data base?

Needle file Over a year ago

why not? portions 32 KB. very effectively instead of the 100,000 individual inserts. this way I am monthly import up to 10 GB of csv files in the production database

Collectives™ on Stack Overflow

How do I increase performance on data load for large text file [duplicate]

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related