CODEWITHSUNDEEP
javasqlite
drublik
drublik

Reputation: 466

Create and import a big amount of data into a SQLite database from Java efficiently

In one process, in server side with Java, I've to create a SQLite database and import a big amount of data (1.200.000 rows divided in 3 tables) and let the user download the generated database.

When I though that there wasn't any way to do it faster, I realized that with the sqlite command line tool, I can execute ".import csv_file tablename" and it imports the data much faster, so I can export my data to CSV files and then import them with that tool. However, I would prefer to do not have to call any command line tool from the Java code but I've not found any way to execute the same import using the JDBC driver. So my questions are:

EDIT: The code & SQLite schema:

I can't show the full schema definition but there are 3 tables created as follows:

CREATE VIRTUAL TABLE xxxxx USING rtree(...) with 5 attributes.
CREATE VIRTUAL TABLE xxxxx USING fts3 (...) with 1 attribute.
CREATE TABLE poidata(xxxxx) with 15 attributes.

There isn't any foreign key between the tables and there isn't any index apart than the PKs.

Regarding the code, it is simple. I generate all the inserts in a StringBuffer and finally I execute:

Statement st = this.getConnection().createStatement();
st.execute("begin transaction");
st.executeUpdate(sql.toString());
st.execute("end transaction");
st.executeUpdate("backup to " + destination.getAbsolutePath());

I'm currently using the Xerial SQLite JDBC driver.

Upvotes: 1

Views: 1327

Answers (1)

drublik
drublik

Reputation: 466

After some optimizations, now it takes around 15 seconds to generate the database. After all the research and tests, the most important thigs are:

  • Use only one transaction for all the inserts, and the transaction has to be declared explicitally. If it is not declared, by default it creates one new transaction for each insert.

  • Create one and only one PreparedStatement to do all the inserts and reuse it as much as possible.

Upvotes: 2

Related Questions