3 ms·
I made a mistake -- it's an order of magnitude slower. That means a single transaction is within the threshold for human perception. If you block the UI thread
by ori_b 6y ago
I made a mistake -- it's an order of magnitude slower. That means a single transaction is within the threshold for human perception. If you block the UI thread on a transaction, you're now dropping 6 frames at 60fps. If you block other operations on the transaction completing, you end up with lag.
Reference: https://www.sqlite.org/faq.html https://www.sqlite.org/faq.html, question 19. (I've seen similar when testing on SSDs locally).
- lostmyoldone 6y agoIf you have an FPS or interactivity target that needs to be guaranteed to be better than about 0.1-1 second - depending on platform - you can't have any disk IO at all on the UI thread, or any thread that's supposed to react at interactive rates at all times. It has generally become much better in the last decade or two, but one should still expect most OS's to sometimes pause for excessive amounts of time on disk IO unless the API is specifically guaranteed to never pause. Even then one would be wise to measure/log deviations if it's critical for the application. OS guarantees might also be contingent on driver/subsystem guarantees, and bad drivers might sometimes affect what seems completely unrelated upstream systems.
- ori_b 6y ago> If you have an FPS or interactivity target that needs to be guaranteed to be better than about 0.1-1 second - depending on platform - you can't have any disk IO at all on the UI thread, or any thread that's supposed to react at interactive rates at all times. Yes, and other file formats encourage doing things in memory, so you don't have any disk i/o in the common path. Using sqlite as a file format strongly discourages the simple, jank-free until you press save workflow of slurping your content, operating on it in memory, and then outputting it all in one operation as a response to an explicit user action. Instead, your whole application gets small but perceptible delays across all operations and interactions.
- em500 6y agoYour concerns seem pretty theoretical. Several very widely used applications (Whatsapp, iMessage, Apple Notes/Calendar) use sqlite as a file format without much issue.
- ori_b 6y agoIt's also recommended against by Firefox because it's been causing performance issues: https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Ne...
- sdinsn 6y agoIt's funny you use that as an example, since Firefox itself uses SQLite as the application file format for history and bookmarks...
- lxdesk 6y agoThat conflates the use of the schema with the use of the format features. You can, in fact, use your custom in-memory structures and then "slurp it out" to BLOB when you're ready to save.
- ori_b 6y agoYes. But if you go that way, though, sqlite is just a slow and clunky format compared to, eg, json or protobuf. I doubt that any program out there uses sqlite this way, other than those which realize they painted themselves into a performance corner and hacked around it for compatibility.
- lalaland1125 6y agoWhy would you block the UI thread on a transaction?
- ori_b 6y agoBecause you've got another operation triggered by a UI action, and you need the previous one to finish, for example.
- yellowapple 6y agoSure, but you shouldn't need to block the thread for this. Most applications that have to deal with this sort of thing would present a loading indicator or something instead while a different thread is busy doing the actual transaction work. This is both possible and encouraged with (to my knowledge) literally every desktop or mobile UI framework written in the last 20 years. And to be clear: this will still be user-visible with literally any other file format. The slow part ain't SQLite itself; it's the disk to which you're writing.
- sdinsn 6y agoThat doesn't answer the question. You never should have to block the UI thread. That's just poor application design, independent of the file format; you cannot blame that on SQLite.
- carlhjerpe 6y agoIf the user interface depends on synchronous IO something is very wrong.