search

avinassh / fast-sqlite3-inserts

Some bunch of test scripts to generate a SQLite DB with 1B rows in fastest possible way
MIT License
381 stars 38 forks source link

Export the random generation code as a virtual table in sqlite3 #14

Open NobodyXu opened 3 years ago

NobodyXu commented 3 years ago

Signed-off-by: Jiahao XU Jiahao_XU@outlook.com

NobodyXu commented 3 years ago

Here's (part of) the benchmark result on my i7-8750H:

Sat Jul 24 21:39:03 AEST 2021 [RUST] basic.rs (100_000_000) inserts

real    3m12.667s
user    3m10.232s
sys 0m1.746s

Sat Jul 24 21:42:16 AEST 2021 [RUST] basic_batched_wp.rs (100_000_000) inserts

real    2m26.269s
user    2m24.799s
sys 0m1.470s

Sat Jul 24 21:44:43 AEST 2021 [RUST] threaded_str_batched.rs (100_000_000) inserts

real    2m17.571s
user    2m32.413s
sys 0m2.181s

Sat Jul 24 21:47:01 AEST 2021 [RUST] basic_prep.rs (100_000_000) inserts

real    0m59.657s
user    0m58.895s
sys 0m0.760s

Sat Jul 24 21:48:02 AEST 2021 [RUST] basic_batched.rs (100_000_000) inserts

real    0m24.746s
user    0m23.856s
sys 0m0.890s

Sat Jul 24 21:48:27 AEST 2021 [RUST] threaded_batched.rs (100_000_000) inserts

real    0m25.210s
user    0m33.036s
sys 0m5.265s

Sat Jul 24 21:48:53 AEST 2021 [RUST] vtable.rs (100_000_000) inserts

real    0m23.487s
user    0m23.077s
sys 0m0.410s
NobodyXu commented 3 years ago

I've requested a PR rusqlite/rusqlite#999 to rusqlite to enable LTO support when using clang-like compiler and using feature "bundled" in libsqlite-sys, which might also improve performance of the vtable.rs.

avinassh commented 3 years ago

Hey! Thanks for the PR. I ran the code, I don't see much difference 🤔 

Mon Jul 26 19:41:26 IST 2021 [RUST] threaded_batched.rs (100_000_000) inserts

real    0m34.875s
user    0m46.887s
sys 0m4.503s

Mon Jul 26 19:42:03 IST 2021 [RUST] vtable.rs (100_000_000) inserts

real    0m34.244s
user    0m29.748s
sys 0m2.527s

I added SQLite optimisations, ran again. It did not make much difference either: 

Mon Jul 26 19:42:03 IST 2021 [RUST] vtable.rs (100_000_000) inserts

real    0m34.818s
user    0m29.118s
sys 0m2.768s
NobodyXu commented 3 years ago

While it’s true that the “real” execution time of vtable isn’t faster, it takes less sys time and also uses only one thread, which mean it uses less resource to accomplish the same task.

On the up side, I am preparing a PR for statically linking sqlite3 with LTO enabled, after that PR, vtable might run faster.