How do I get SQLite to run faster with PRAGMA synchronous = OFF by default

Question

I just created a new SQLite database from the command line and I have a ODBC connection to the SQLite database.
If I issue it queries, by default, the synchronous seems to be "ON" which is really slowing down INSERT performance big-time.

How do I get a SQLite database to default to PRAGMA synchronous = OFF without needing to send a SQL command for it to do so? Can i create a .INI file or something to enable it?

Answer 1

Unfortunately for everyone, after letting this question sit for 6 days, I finally figured out the answer. The SQLite ODBC driver seems to be the answer. If you create a DSN, it allows you to set Sync to "OFF" in the DSN settings. Very convenient.

Answer 2

SQLite runs on FULL synchronous by default. There is no INI, nothing to change except when connected. However, this need only be set once per session, so you could change your project's connection function to add the "PRAGMA synchronous = OFF" command after the connection. That would be actually the cleanest and quickest approach.

But if you truly want SQLite to open your database with synchronization off by default, you might want to recompile SQLite with a different default.

For the current release (3.7.3), look for variable safety_level in sqlite.c of the sqlite-amalgamation source:

Change:

safety_level = 3;

To:

safety_level = 1;

(Yes, it's one off from the shell setting.) In the openDatabase function (and attachFunc if you wish).

---

If you really need this process accelerated, as stated in the comments you will have at the very least to consider transactions. Down the road, this is the preferred solution. It might not be the easiest, or the most possible (time is after all limited to all), but it's the cleanest, sanest, easiest to maintain in the long term. (I just had to get that off my chest. Done!)

Answer 3

In Java I use following snippet on getConnection method.So whenever I get new connection, synchronous will be disabled.

    Connection con = DriverManager.getConnection("jdbc:sqlite:lms.db");        
    Statement st = con.createStatement();        
    String sql="PRAGMA synchronous=OFF";
    st.execute(sql);