huanld/speckle-sharp-sdk
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
jrm/avoid-deprecated-fields
speckle-sharp-sdk/notes/sqlite-performance.md
T
Adam Hathcock afde379cda trying to move core
2024-06-21 08:42:42 +01:00

356 lines
13 KiB
Markdown
Raw Permalink Blame History

This file contains invisible Unicode characters
This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
## Storage Size
1 million objects => 540mb ( based on ~= 4.2 million objects => 2.3GB not gzipped)
1 million objects => 127mb gzipped
4x reduction in space
## Local storage takeaways:
SQLite optimisations make a difference in insertion speed. Insertion speed does slow down on large tables (+1m rows).
Partioned tables (by, for example, the first two decimals of the hash) have slower but predictable insertion speed. Not sure if compromise is worth it?
## Even More Optimised single object table
Optimisations are:
- `PRAGMA journal_mode = MEMORY;`
- `PRAGMA synchronous = OFF;`
- `PRAGMA count_changes=OFF;`
- `PRAGMA temp_store=MEMORY;`
### Test 1
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 2286 ms -> 50000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 1426 ms -> 100000 objects per second
-------------------------------------------------
### Test 2
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 3052 ms -> 33333.333333333336 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 2244 ms -> 50000 objects per second
-------------------------------------------------
### Test 3
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 4941 ms -> 25000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 2555 ms -> 50000 objects per second
-------------------------------------------------
### Test 4
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 8022 ms -> 12500 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 3350 ms -> 33333.333333333336 objects per second
-------------------------------------------------
### Test 5
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 6602 ms -> 16666.666666666668 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 3445 ms -> 33333.333333333336 objects per second
-------------------------------------------------
### Test 5+: A couple of more rounds, pushing objs to 2.000k
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 7332 ms -> 14285.714285714286 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 7625 ms -> 14285.714285714286 objects per second
-------------------------------------------------
-------------------------------------------------
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 7539 ms -> 14285.714285714286 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 4249 ms -> 25000 objects per second
-------------------------------------------------
-------------------------------------------------
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 8300 ms -> 12500 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 7289 ms -> 14285.714285714286 objects per second
-------------------------------------------------
-------------------------------------------------
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 8668 ms -> 12500 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 8060 ms -> 12500 objects per second
-------------------------------------------------
Starting to save 100000 of objects
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 10228 ms -> 10000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 11475 ms -> 9090.90909090909 objects per second
-------------------------------------------------
-------------------------------------------------
Starting to save 100000 of objects
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 12540 ms -> 8333.333333333334 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 7113 ms -> 14285.714285714286 objects per second
-------------------------------------------------
-------------------------------------------------
Starting to save 100000 of objects
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 17153 ms -> 5882.35294117647 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 5997 ms -> 20000 objects per second
-------------------------------------------------
-------------------------------------------------
Starting to save 100000 of objects
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 20841 ms -> 5000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 9195 ms -> 11111.111111111111 objects per second
-------------------------------------------------
Starting to save 100000 of objects
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 13404 ms -> 7692.307692307692 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 7529 ms -> 14285.714285714286 objects per second
-------------------------------------------------
-------------------------------------------------
Starting to save 100000 of objects
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 19806 ms -> 5263.1578947368425 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 7318 ms -> 14285.714285714286 objects per second
-------------------------------------------------
-------------------------------------------------
Starting to save 100000 of objects
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 24612 ms -> 4166.666666666667 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 7410 ms -> 14285.714285714286 objects per second
-------------------------------------------------
-------------------------------------------------
Starting to save 100000 of objects
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 22257 ms -> 4545.454545454545 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 18699 ms -> 5555.555555555556 objects per second
-------------------------------------------------
-------------------------------------------------
Starting to save 100000 of objects
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 20947 ms -> 5000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 14089 ms -> 7142.857142857143 objects per second
-------------------------------------------------
## Optimised single object table
Optimisations are: `PRAGMA journal_mode = MEMORY;` and `PRAGMA synchronous = OFF;`
### Test 1:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 2267 ms -> 50000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 1327 ms -> 100000 objects per second
-------------------------------------------------
### Test 2:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 4532 ms -> 25000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 2243 ms -> 50000 objects per second
-------------------------------------------------
### Test 3:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 3768 ms -> 33333.333333333336 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 5295 ms -> 20000 objects per second
-------------------------------------------------
### Test 4:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 4033 ms -> 25000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 3126 ms -> 33333.333333333336 objects per second
-------------------------------------------------
### Test 5:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 4432 ms -> 25000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 3527 ms -> 33333.333333333336 objects per second
-------------------------------------------------
## Single object table
### Test 1:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 11964 ms -> 9090.90909090909 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 6875 ms -> 16666.666666666668 objects per second
-------------------------------------------------
200k total in db
### Test 2:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 21956 ms -> 4761.9047619047615 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 8904 ms -> 12500 objects per second
-------------------------------------------------
400k total in db
### Test 3:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 25532 ms -> 4000 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 10124 ms -> 10000 objects per second
-------------------------------------------------
600k total in db
### Test 4:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 26629 ms -> 3846.153846153846 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 10610 ms -> 10000 objects per second
-------------------------------------------------
800k total in db
### Test 5:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 26956 ms -> 3846.153846153846 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 11007 ms -> 9090.90909090909 objects per second
-------------------------------------------------
1000k total in db
## Bucketed Object Table (256 individual tables for objects):
Pre-generate 256 tables, of form `objs${prefix}`, where prefix is the cartesian product of all the valid hex decimals (`0-9, a-f`).
### Test 1:
Forgot to copy paste.
200k total in db
### Test 2:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 19096 ms -> 5263.1578947368425 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 7401 ms -> 14285.714285714286 objects per second
-------------------------------------------------
400k total in db
### Test 3:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 22477 ms -> 4545.454545454545 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 8668 ms -> 12500 objects per second
-------------------------------------------------
600k total in db
### Test 4:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 23438 ms -> 4347.826086956522 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 9288 ms -> 11111.111111111111 objects per second
-------------------------------------------------
800k total in db
### Test 5:
-------------------------------------------------
BufferedWriteTest: Wrote 100000 in 23735 ms -> 4347.826086956522 objects per second
-------------------------------------------------
-------------------------------------------------
BulkWriteMany: Wrote 100000 in 9944 ms -> 11111.111111111111 objects per second
-------------------------------------------------
1mil total in db
Reference in New Issue View Git Blame Copy Permalink