I am implementing application specific data import feature from one database to another.

I have a CSV file containing say 10000 rows. These rows need to be inserted/updated into database.

There might be the case, where couple of rows may present in database that means those need to be updated. If not present in database, those need to be inserted.

One possible solution is that, I can read one by one line, check the entry in database and build insert/update queries accordingly. But this process may take much time to create update/insert queries and execute them in database. Some times my CSV file may have millions of records.

Is there any other faster way to achieve this feature?

  • Try to process it in parts or else a large CSV reading at one shot will result in OutOfMemory ! – The New Idiot Jul 17 '13 at 5:56
  • @TheNewIdiot that won't happen if using enough memory like a decent server that destinies at least 2 GB ram to the JVM. It will also depend on the kind of data in the CSV file and if the process will run in a single process or next to other processed in the server. – Luiggi Mendoza Jul 17 '13 at 5:58
  • @Luiggi Mendoza: I agree with you. We have enough memory to process the large CSV file in production. – Narendra Verma Jul 17 '13 at 6:05

There's a nice technology available in Oracle called External Tables. In your scenario, you could access your external plain-text data using External Tables from within the database and update your existing data in database with SQL statements you love and are used to – for example, INSERT, MERGE etc.

In most cases, using Oracle supplied utilities is the best way to perform ETL. And because your question sounds more like administrative one I suggest you to look at my previous post on DBA Stack Exchange "Update Oracle database from CSV".

UPDATE: This approach works pretty well for reading external data in database. Generally, you define external data format every time you need to process the plain-text file which has new format. Once external table is created you can query it just like a real database table. Whenever there is a new data to import, you just replace the underlying file(s) on the fly without need to recreate external table(s). Since external table can be queried as any other database table, you can write SQL statements to populate other database tables.

The overhead of using External Tables is usually lower compared to other techniques you would implement manually because this technology was designed with performance in mind taking into account the Oracle Database architecture.

| improve this answer | |
  • I agree this is one of the solution to achieve my goal. How this approach can be fit for dynamic CSV processing? Means, my application user have opportunity to upload multiple files with different formats (in this case external tales need to be created on the fly). Also, one CSV file may contain data which needs to be populated into multiple tables. – Narendra Verma Jul 17 '13 at 6:14

I think you should use SQL*Loader to load CSV file into temporary table and then use MERGE statement to insert data into working table.
SQL*Loader will give you more flexibility then external tables and if one uses direct path load it is really fast. And MERGE will do exactly what you need - INSERT new records and UPDATE existing ones.
Couple of links to start:

| improve this answer | |
  • 1
    When you load data into the database using SQLLoader the DBWR process or SQLLoader process write buffers to data files. When you subsequently move loaded data to other tables the database performs another I/O. I don't think this extra work can be justified. By the way when External Tables utilizes ORACLE_LOADER driver the syntax for defining the input data format is the same used by sqlldr utility because essentially they are the same technology and thus can be used interchangeably. External Tables in this scenario is preferred since there's no need to first load data into the database – Yasir Arsanukaev Jul 17 '13 at 22:47
  • As usual answer is "it depends" :). In our case it's usually more convenient to load into temp table first and process later. Since direct path load does not generate redo that additional I/O is almost unnoticable among other operations. In other cases of course other methods will be better. – Mindaugas Riauba Jul 18 '13 at 14:01

PreparedStatements will make the creation of insert or update queries very fast. You should have three PreparedStatements: One for insert, one for update, and one for checking if the row is already in the table. If you are able to keep the IDs the same between the CSV file and the new database, then checking to see if a row is present using the primaryID field should also be very fast.

Using a batch insert may offer a performance gain. As you stream through the CSV file, you would then check if the row is already there and then either do an update or add the row to your batch insert command. You should check this SO question for speed comparison of these two approaches.

If this database import is something that needs to be done regularly and performance is an issue using the method I outlined above, then you can try handling the task with multiple worker threads. Use as many threads as their are processors on the machine running this code.

  int nThreads = Runtime.getRuntime().availableProcessors();

Each thread gets its own DB connection and as your code iterates through the file, lines of CSV could be handed off to the various threads. This is much more complicated, so I would only do this if the performance requirements forced me to.

| improve this answer | |
  • Thanks for your reply. Again, this will require CSV file parsing and populating values into prepared statements. With 'External Tables' approach, I see file parsing can be moved to Database side where application does not need to care about it. Also, I am using JPA with Hibernate in my application. I am looking for the option which can be the combination of JPA/Hibernate/Oracle which facilitates no file parsing, good performance, maintanable and flexible. – Narendra Verma Jul 17 '13 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy