Try removing the VACUUM call, there's no empty pages to defragment anyway.

Furthermore, you're doing a bunch of PRAGMA calls that may or may not help. Try not doing those.

Thanks wmertens for your help

originally I had:

database was writed in 1.3185472186693612 seconds.
text was writed in 0.11124303232645616 seconds.

without VACUUM and pragma:

database was writed in 0.5217875576733301 seconds.
text was writed in 0.11288163432618603 seconds.

It is 5 times slower than classical filesystem write

If I'm reading your Python correctly, you seem to be trying to do all of the following on every write:

1. Open an SQLite database connection.
2. Configure that connection.
3. Do the write.
4. Vacuum.
5. Close the connection.

I suspect you are spending most of the CPU time in steps 1, 2, 4, and 5. I suggest you revise your program so that it opens a single database connection, just once, configures it just once, then do lots of writes. Then at the very end, vacuum if you think you should (you probably don't need to) and then close the connection. I think that will go a lot faster.

I repeat 3 times the writes process and compute the mean time.

I try to do a fair comparison with the classical way wich is:

1. open file
2. do the write
3. close file

I updated the code to remove time spent on SQLite database connection and configuration as we coud consider that these operations are done only a few time during an application life

./write_perf_db_txt.py
database was writed in 0.5052063270122744 seconds.
text was writed in 0.10451052167142431 seconds.

Of course if you remove all the overhead of parsing the SQL, parsing the parameters, allocating cells to store the records, and storing the records, then a simple sequential file is faster.

Now, compare the speed of retrieving the n'th record of data.

The SQLite3 database can do that in a few MICROSECONDS while it will take you (on average), 50 milliseconds to do that since you will have to read and discard n-1 records before you get to the record you want. The SQLite3 database will just return the single record that you wanted directly.

So your "sequential file" is 50,000 times slower than SQLite3 at retrieving data.

Assuming that you are merely writing all your records once, you only need to retrieve more than 3 random records, on average, before the "speed" of using an SQLite3 database is "faster" that your sequential file.

So yes, a sequential file accessed entirely sequentially is superior to a database accessed entirely sequentially. And how is this surprising?

Thanks kmedcalf for your help and insight

I though that, as a row size can be estimated (sum of max size for each fields), sqlite was able to write a batch of row in parrallel and take advantage of SSD. While a sequential write can not.

SQLite does not use the same amount of space for each row. Different integers take different numbers of bytes to store. Strings take the amount of space their length needs. NULL fields take just one byte, or sometimes no bytes at all.

I think the sqlite open->write->close, open->write->close,open->write->close logic is a little odd for a database

databases are more like open-> Insert to a tableA, insert to tableB, insert to tableC, -> close

So to be complete I updated the code rev3 in order to bench write rows into the database vs sequential write.

And added a second part which bench to select row if the first field is into a list. I compare too with and without primary key (constraint).

text was writed in 0.10 seconds.
database was writed in 0.58 seconds.
database with constraint was writed in 1.25 seconds.
text selected in 0.32 seconds.
database selected in 0.00 seconds.
database with constraint selected in 0.00 seconds.

SQLite also does extra work on the write-path compared to your text implementation, to ensure durability. ie. it calls fsync() to ensure the data is actually on disk.

You can try adding this for benchmarking purposes: PRAGMA synchronous=OFF

But note that this is not considered a safe option in production - if a machine loses power mid-transaction then you'll end up with a corrupt database.

Another way to get much the same effect is to do all of your inserts within a single explicit SQL transaction, calling COMMIT only once at the end. It's a common pattern for importing bulk data into any SQL DB for this very reason.

The sqlite3 wrapper in Python does this automagically unless you have turned the automagic setting to "manual". The wrapper calls this the isolation_level of the connection and is controlled by a connection property (and connect method parameter) of the same name. How well this magic works depends on the particular version of pysqlite2 that is being used (the sqlite3 wrapper), and thus the version and release date of the version of python being used.

The sample uses the default setting so the first "insert" on the connection will begin a deferred transaction which is committed when the .commit method of the connection is called.

The .executemany method prepares the SQL statement and then binds and steps the resulting statement for each member of the list of bind parameters.

Yeah I figured all the inserts must have been magically batched into a single transaction, otherwise the SQLite write times would have been much slower.