Inserting data with UCanAccess from big text files is very slow

Question

I'm trying to read text files .txt with more than 10.000 lines per file, splitting them and inserting the data in Access database using Java and UCanAccess. The problem is that it becomes slower and slower every time (as the database gets bigger).

Now after reading 7 text files and inserting them into database, it would take the project more than 20 minutes to read another file.

I tried to do just the reading and it works fine, so the problem is the actual inserting into database.

N.B: This is my first time using UCanAccess with Java because I found that the JDBC-ODBC Bridge is no longer available. Any suggestions for an alternative solution would also be appreciated.

Gord Thompson · Accepted Answer · 2016-05-25T14:47:57.623

If your current task is simply to import a large amount of data from text files straight into the database, and it does not require any sophisticated SQL manipulations, then you might consider using the Jackcess API directly. For example, to import a CSV file you could do something like this:

String csvFileSpec = "C:/Users/Gord/Desktop/BookData.csv";
String dbFileSpec = "C:/Users/Public/JackcessTest.accdb";
String tableName = "Book";

try (Database db = new DatabaseBuilder()
        .setFile(new File(dbFileSpec))
        .setAutoSync(false)
        .open()) {

    new ImportUtil.Builder(db, tableName)
            .setDelimiter(",")
            .setUseExistingTable(true)
            .setHeader(false)
            .importFile(new File(csvFileSpec));

    // this is a try-with-resources block, 
    //     so db.close() happens automatically
}

Or, if you need to manually parse each line of input, insert a row, and retrieve the AutoNumber value for the new row, then the code would be more like this:

String dbFileSpec = "C:/Users/Public/JackcessTest.accdb";
String tableName = "Book";
try (Database db = new DatabaseBuilder()
        .setFile(new File(dbFileSpec))
        .setAutoSync(false)
        .open()) {

    // sample data (e.g., from parsing of an input line)
    String title = "So, Anyway";
    String author = "Cleese, John";

    Table tbl = db.getTable(tableName);
    Object[] rowData = tbl.addRow(Column.AUTO_NUMBER, title, author);
    int newId = (int)rowData[0];  // retrieve generated AutoNumber
    System.out.printf("row inserted with ID = %d%n", newId);

    // this is a try-with-resources block, 
    //     so db.close() happens automatically
}

To update an existing row based on its primary key, the code would be

Table tbl = db.getTable(tableName);
Row row = CursorBuilder.findRowByPrimaryKey(tbl, 3);  // i.e., ID = 3
if (row != null) {
    // Note: column names are case-sensitive
    row.put("Title", "The New Title For This Book");
    tbl.updateRow(row);
}

Note that for maximum speed I used .setAutoSync(false) when opening the Database, but bear in mind that disabling AutoSync does increase the chance of leaving the Access database file in a damaged (and possibly unusable) state if the application terminates abnormally while performing the updates.

thanks but I have to split the file and use substring in every line so it require sql querys and String splitting. — Gregor Samsa, May 24 '16 at 19:45
@Yassine - If you are using SQL (e.g., via PreparedStatement) to insert one row at a time then you could also accomplish the same result using Jackcess and its AddRow method. That way you could still avoid the UCanAccess/HSQLDB overhead. — Gord Thompson, May 24 '16 at 20:10
yes I'm using PreparedStatement to insert each row (basing on its first 3 characters) after splitting it with subString and stocking it into variables, how can I use Jackcess and addRow method ? N.B: I'm using AUTO_GENERATED_KEY to return the primary key of the inserted row to use it after the insertion. does addRow method have this option ? — Gregor Samsa, May 24 '16 at 20:25
Thanks a lot, I made the question answered but there is still one problem, I want to update a table based on its primary key, I used UCanAccess but when the table is opened with jackcess it won't get updated ?? how can I update it with jackcess ? — Gregor Samsa, May 25 '16 at 13:54
@YassineBHS - Yes, Jackcess can update the Access database. That's how UCanAccess does it! I have updated my answer. — Gord Thompson, May 25 '16 at 14:49
the speed of the application inserting to databse increased but it still take more than 7 minutes to do it when the text file has +10.000 lines :(( — Gregor Samsa, May 25 '16 at 14:55
@YassineBHS - Well, performance depends on a lot of factors, but I just tested an upsert of 10 thousand rows into an Access database via Jackcess and it consistently took less than 5 seconds. — Gord Thompson, May 25 '16 at 16:58
OK, maybe it's because I insert and update or maybe it's the UCanAccess I opened to test before the inserting. anyway Thanks a lot — Gregor Samsa, May 25 '16 at 20:30

score 0 · Answer 2 · answered May 25 '16 at 14:47

0

Also, if you need to use slq/ucanaccess, you have to call setAutocommit(false) on the connection at the begin, and do a commit each 200/300 record. The performances will improve drammatically (about 99%).

answered May 25 '16 at 14:47

jamadei

1,700
9
8

how to do a commit each 200/200 record ? – Gregor Samsa May 25 '16 at 14:57
just do a count and call commit on the connection only when the counter says you have already inserted 200 records... – jamadei May 25 '16 at 16:35

Inserting data with UCanAccess from big text files is very slow

2 Answers2