SQLite Forum

Timeline
Login

12 forum posts by user bioinfornatics

2020-03-15
01:20 Edit reply: sqlite write performance is slower than the filesystem (artifact: a7678f6f3d user: bioinfornatics)

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.
01:19 Reply: sqlite write performance is slower than the filesystem (artifact: cc5fbae781 user: bioinfornatics)

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.73 seconds.
database with constraint was writed in 1.24 seconds.
text selected in 0.31 seconds.
database selected in 0.00 seconds.
database with constraint selected in 0.00 seconds.
2020-03-14
22:01 Reply: sqlite write performance is slower than the filesystem (artifact: 315767ded3 user: bioinfornatics)

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.

21:41 Edit reply: sqlite write performance is slower than the filesystem (artifact: f1efa969de user: bioinfornatics)

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.
21:41 Edit reply: sqlite write performance is slower than the filesystem (artifact: dd91758192 user: bioinfornatics)

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 this operation is 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.
21:40 Reply: sqlite write performance is slower than the filesystem (artifact: 04a6bb5af6 user: bioinfornatics)

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 this operation is 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.
20:34 Edit reply: sqlite write performance is slower than the filesystem (artifact: 1a70a98f87 user: bioinfornatics)

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

20:11 Delete reply: sqlite write performance is slower than the filesystem (artifact: 7867c186af user: bioinfornatics)
Deleted
20:11 Edit reply: sqlite write performance is slower than the filesystem (artifact: 7e57c17483 user: bioinfornatics)

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 6 times slower than classical filesystem write

20:10 Reply: sqlite write performance is slower than the filesystem (artifact: c76876403f user: bioinfornatics)

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 6 times slower than classical filesystem write

20:10 Reply: sqlite write performance is slower than the filesystem (artifact: 56acd13e0f user: bioinfornatics)

Thanks wmertens for your help

originally I had:

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

without VACUUM pragma:

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

It is 6 times slower than classical filesystem write

18:30 Post: sqlite write performance is slower than the filesystem (artifact: 1a1c61cb01 user: bioinfornatics)

Dear,

I wrote a little script (availlable here) to compare i/o perf sqlite vs raw tabulated file in python.

The result it is a big win for the raw text writer as it is 10 times faster than sqlite writer.

While sqlite claims to be faster than the filesystem.

Is it possible to speedup sqlite ?

As sqlite does not enforce the length of a VARCHAR it seem that sqlite can not split rows to insert them by block by guessing the maxmimal size that can take a row.

So If I use SQLITE_MAX_LENGTH does the sqlite engine will optimize prepared insert statement with the use of multithread ?

Thanks for your insight

Have a nice day

best regards