Hibernate + enabling SQLite Pragmas to gain speed on Windows 7 machine

Used software:

• hibernate 3.6
• sqlite jbdc 3.6.0
• java jre 1.6.X

I have a problem with transferring data over a tcp connection ( 20 000 entrys )

• create a sqlite database with the help of hibernate
• use hibernate开发者_JAVA技巧view and hibernate annotations to create querys
• hibernate proberties are also used
• storing 20 000 entries with hibernate and NO sqlite pragmas enabled lasts nearly 6 minutes ( ~ 330 sec) on Windows 7
• storing 20 000 entries without hibernate and all relevant sql pragmas enabled lasts ca 2 minutes ( ~ 109 sec ) on windows 7
• tests with hibernate and sqlite without pragmas on windows XP and windows Vista run fast, but on win7 it lasts nearly 3 times ( ~ 330 sec - win 7) as long as on the XP machine
• on windows 7 we want to activate sqlite pragmas to gain speed boost

• relevant pragmas are:

  PRAGMA cache_size = 400000;
  PRAGMA synchronous = OFF;
  PRAGMA count_changes = OFF;
  PRAGMA temp_store = MEMORY;
  PRAGMA auto_vacuum = NONE;
  

Problem: we must use hibernate ( no Nhibernate ! )

Questions:

• how to enable these pragmas in hibernate sqlite connection if its possible?
• Is it possible to do so with using hibernate?

I was also looking for some way to set another pragma: PRAGMA foreign_keys = ON for hibernate connections. I didn't find anything on the subject and the only solution I came up with is to decor SQLite JDBC driver and set required pragma each time new connection is retrieved. See sample code below:

@Override
public Connection connect(String url, Properties info) throws SQLException {
    final Connection connection = originalDriver.connect(url, info);
    initPragmas(connection);
    return connection;
}

private void initPragmas(Connection connection) throws SQLException {
    //Enabling foreign keys
    connection.prepareStatement("PRAGMA foreign_keys = ON;").execute();
}

Full sample is here: https://gist.github.com/52dbc7066787684de634. Then when initializing hibernate.connection.driver_class property just set it to your package.DriverDecorator

Inserts one-by-one can be very slow; you may want to consider batching. Please see my answer to this other post.

for PRAGMA foreign_keys = ON equivalent

hibernate.connection.foreign_keys=true

or

<property name="connection.foreign_keys">true</property>

depending on your strategy