Geek Answers Handbook

How to make SQLite run faster with PRAGMA synchronous = OFF by default

I just created a new SQLite database from the command line and I have an ODBC connection to the SQLite database.
If I issue its requests, the default synchronous appears to be "ON", which really slows down INSERT in large numbers.

How do I get the default SQLite database PRAGMA synchronous = OFF without having to send an SQL command for this? Can I create a .INI file or something to include it?

+4
source share
4 answers

Unfortunately for everyone, after this question has been sitting for 6 days, I finally understood the answer. SQLite ODBC answer. If you are creating a DSN, this allows you to set Sync to “OFF” in the DSN settings. Very comfortably.

+10
source

SQLite works by default FULL synchronously. No INI, nothing will change except when connected. However, this needs to be set only once per session, so you can change the connection function to the project to add the command "PRAGMA synchronous = OFF" after connection. This will be the cleanest and fastest approach.

But if you really want SQLite to open your database with default synchronization, you may need to recompile SQLite with a different default value.

For the current version (3.7.3), find the safety_level variable in sqlite.c of the sqlite-amalgamation source:

Edit:

 safety_level = 3;

To:

 safety_level = 1;

(Yes, this is one of the parameters of the shell). In the openDatabase function (and attachFunc , if you want).

If you really need to speed up this process, as stated in the comments, you will at least consider transactions. This is the preferred solution along the way. It may not be the easiest or the most affordable (time is limited for everyone), but it is cleaner, safer, easiest to maintain in the long run. (I just needed to take it off my chest. Done!)

+3
source

In Java, I use the following snippet of the getConnection method. Therefore, whenever I get a new connection, synchronous will be disconnected.

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

A lot of time has passed since the last answer in this thread, but if someone like me was looking for a way to set PRAGMA synchronous = OFF, but could not maintain a connection to SQLite (I have my reasons); what I did was set "PRAGMA synchronous = OFF" at the beginning of the request, and then do the usual things later, separating them with a semicolon (;), like this:

 PRAGMA synchronous = OFF; INSERT INTO [...]

I don't know if this is best practice, but it worked for me.

0
source

More articles:


All Articles