#171 ORMLite seems to not be turning on transactions via TransactionManager

Latest release
wont-fix
nobody
None
5
2014-08-17
2014-04-06
Erik Norvelle
No

I am using ORMLite 4.48 under Java 7 on OS X. When performing multiple inserts, SQLite performs very slowly, around 5-10 inserts per second. Following the ORMlite manual, I wrapped the insert-ing code in a TransactionManager.callInTransaction() block. However, there is no speed-up even with transactions available. The code is as follows:

        // Wrap all inserts in a transaction block.
        IndexHegelianus.application.getConnectionSource().getReadWriteConnection().setAutoCommit(false);          
        TransactionManager.callInTransaction(IndexHegelianus.application.getConnectionSource(),
            new Callable<Void>() {
                @Override
                public Void call() throws Exception {
                    // Empty the sections table
                    Section.deleteAll();
                    int currFileNum = 0;

                    // For each file, extract its sections.
                    for (HtmlFile file : files) {
                        String sectionString = file.getTitle();
                        String[] path = StringUtils.split(sectionString, ",");
                        Section.createFromPath(path, file.getId());
                        myForm.getjParsingProgressBar().setValue(++ currFileNum);
                    }
                    return null;
                }
            });

        // Commit transaction, set GUI to terminated state.
  IndexHegelianus.application.getConnectionSource().getReadWriteConnection().setAutoCommit(true);

Where the Section method createFromPath can call Section.create() up to 5 times. The execution time for about 1000 commits is nearly three minutes.

Is ORMLite failing to actually turn the SQLite transaction on, or is there a bug in SQLite itself (which seems unlikely)?

Discussion

  • Gray Watson
    Gray Watson
    2014-04-07

    I suspect neither. I've commented on your SO post. I'm going to mark this as not a bug since although there may be a performance problem, I have good test coverage on transactions and Sqlite.

     
  • Gray Watson
    Gray Watson
    2014-04-07

    I'm closing this for now and will re-open if we actually think there is a bug here.

     
  • Gray Watson
    Gray Watson
    2014-04-07

    • status: open --> wont-fix
     
  • Erik Norvelle
    Erik Norvelle
    2014-04-07

    I appreciate your response. As I mentioned in the SO post, I cannot find any way to access the low-level JDBC api in my code, since ORMLite manages its own Connection through the ConnectionSource class, if I understand correctly.

    However, I am going to try a workaround that is something of a hack... I will have my ORM-ed class cache all writes to the database until a batch commit method is called; this method will then use low-level DAO methods to start the transaction, write the cached changes, and then perform the commit. We'll see how this works.

     
    Last edit: Erik Norvelle 2014-04-07
  • Gray Watson
    Gray Watson
    2014-04-09

    Again you can set autocommit from the Dao class and you have raw access for other SQL commands. Let me know what JDBC access you are missing.