On May 13, 2018, at 10:15 PM, Rowan Worth <row...@dug.com> wrote: > > ...I wouldn't call the extra i/o imposed by sqlite "very very > cheap" either - it doubles writes (once to the rollback journal, once to > the DB), forces syncs, and likely results in a more seek heavy i/o pattern > (this depends a bit on schema design and whether the app requires/fully > takes advantage of relational operations).
fopen() can be simpler than SQLite when you’ve got a problem that isn’t taking advantage of SQLite’s power. If you ignore the sizes of SQLite and libc, so that you only consider the size of end-user-written code, the same up-front cost gets you a fully ACID-compliant RDBMS on the one hand vs. flat C structures written linearly to disk on the other. If you can express most or all of your operations on the data in terms of linear table scans or linearly-indexed accesses — e.g. data[i] is always at sizeof(data[0]) * i bytes into the file — then the flat file is probably faster and cheaper. On top of that, the binary size of stdio is probably a lot smaller than that of SQLite. The interesting cases happen when you write your “simple” C code using fopen() and then find yourself writing O(N) algorithms where faster algorithms would work if only you had indexed access or JOIN abilities, or… Then you have to do a classic tradeoff evaluation: will the complexity of SQLite pay for itself? I recommend reading “Avoid SQLite in Your Next Firefox Feature,” an article written by the Mozilla developers after they found many features creating SQLite DBs for tiny little things better handled another way: https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature Note that their recommendation to use plain-text JSON or lz4-compressed JSON instead of small amounts of data comes out of the fact that Mozilla had to have those mechanisms in Firefox anyway, so they’re “free,” and they already had to be heavily-optimized. It isn’t great as general-purpose advice. > My point is simply that it's unwise to think of any DB query as having "no > latency" even when dealing with an sqlite DB. Yes. ObWarStory: I once wrote an application using SQLite that tripped a 20-second watchdog timer by having an unexpectedly high amount of real-world traffic: a mere hundreds of DB updates per second. How? It was running on a system with a 5400 RPM hard disk, which translates to a theoretical maximum of 45 transactions per second: 5400 RPM / 60 seconds per minute / 2 disk rotations per transaction = 45. Since each DB update was done independently, as long as the update rate was over 45 TPS, the application would fall further and further behind real time until that watchdog timer tripped. Immediate solution: wrap a couple of key “for” loops in SQLite transactions to batch the disk updates. Future solution: stop using spinning rust. :) _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
