3 ms·
This article doesn't describe any of their methods. > It was clear that serialization was the culprit, so we switched to Protobuf instead of stringifying the e
by mholt 3y ago
This article doesn't describe any of their methods.
> It was clear that serialization was the culprit, so we switched to Protobuf instead of stringifying the embeddings. This resulted in considerable gains. For 100,000 embeddings, writing took 6.2 seconds, reading 12.6 seconds and the disk space consumed dropped to 440 MB.
Doing what though? What was their schema like? What are their indices? Primary keys? Column types? Query structure?
It's lacking so little information I find this article not useful and not interesting to me. Which is a bit disappointing since I was hoping to learn something relevant.
- vishnumohandas 3y agoSorry, we should have linked to the DB Schema. Here you go: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L46 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d...
- maskros 3y agoAutoincrement is usually not needed in SQLite, and adds a lot of overhead to every insertion since it needs to read and update the sqlite_sequence table on every insert. https://www.sqlite.org/autoinc.html https://www.sqlite.org/autoinc.html
- mholt 3y agoOne obvious explanation is the lack of indexing on the column you're searching over. It's currently an opaque blob, but a design that actually uses the database's features will serve you much better. What your article is really saying is, "SQLite isn't a good fit for our data that we didn't want to serialize differently to be efficient with SQLite." And that's fine, it looks like you found the right tool for your data and requirements, but the title is click-baity and isn't really about SQLite.
- SPBS 3y agoLook here: https://github.com/ente-io/edge-db-benchmarks/blob/43273607d7c55982693d498f10df184a06ed5930/lib/databases/sqlite_db.dart#L84 https://github.com/ente-io/edge-db-benchmarks/blob/43273607d... SELECT $columnEmbedding FROM $tableName LIMIT 10000 OFFSET $offset They're not using any indexes, because they're doing offset pagination [1] in a loop. And offset pagination is causing them to scan 550K total rows [2] instead of 100K. OP, change your code. You're probably wrong about SQLite being slower than Isar, or at least it must be very close. [1] https://use-the-index-luke.com/no-offset https://use-the-index-luke.com/no-offset [2] 10K + 20K + 30K + 40K + 50K + 60K + 70K + 80K + 90K + 100K = 550K
- vishnumohandas 3y agoNice catch, thank you! I was playing around with the offsets incorrectly, while attempting to reduce the memory footprint on a low-end Android device. I've updated the code[1] to read all embeddings in one shot, and I'm now measuring the time to read and the time to deserialize separately. On an iPhone simulator running on an Apple M3 Pro, this is what it says: ``` [log] SqliteDB fetch all took: 1572 ms [log] SqliteDB deserialization took: 4268 ms ... [log] Isar: 100000 embeddings retrieved in 616 ms ``` [1]: https://github.com/ente-io/edge-db-benchmarks/commit/e02c3d3811bbfb0296173c960ac219c1f6d90263#diff-f3dd9b936912c331bf9492a92c2f5b6aca5c741b807fab2da43b5f5861fb7077 https://github.com/ente-io/edge-db-benchmarks/commit/e02c3d3...