5 ms·
SQLite Release 3.8.11.1
- shredwheat 11y agoI still remember when Firefox switched to using SQLite for most of its internal data. It felt like it took 3 or 4 releases to work out the concurrency and blocking issues. I remember back in the day it had lots to do with fsync. Has most of that been worked out between SQLite and newer Linux kernels, or is that a pit waiting for other applications that go the same route?
- applecore 11y agoI'd like to see the same speed measurements done for mobile. A 64-bit Linux desktop isn't representative of the #1 use case for SQLite as a mobile database replacement for Core Data.
- thomaslutz 11y agoWhy should it not be similar faster on mobile?
- gburt 11y agoDifferent instruction set. The release notification isn't very clear about what optimizations took place. It is quite possible that it will not be the same result on a different architecture.
- vvanders 11y agoEven moreso, different cache sizes. Some object that might fit nicely in a 4kB cache might get destroyed with a 1kB cache.
- Zergy 11y agoHe never said it shouldn't, just that he would be interested in seeing the numbers. I would be too since they would be more relevant to my use case.
- em3rgent0rdr 11y agoNo only different instruction set, but probably drastically different cache hierarchy and branch predictors. The tool cachegrind which they are using is simulating a program's effect on the cache and branch predictor, which will likely both be dramatically smaller in embedded devices vs modern server CPUs, so the results may be very misleading to real world applications for sqite.
- pcwalton 11y agoAlso multicore memory hierarchies are very different on ARM -- e.g. atomics are much more expensive there in my experience.
- DrJokepu 11y agoI've never worked with Core Data, but I assume it always uses the SQLite version that ships with iOS (as opposed to a version you ship with your app), so that means that you wouldn't see these improvements on iOS for at least a year or so anyway, or am I wrong about this?
- kbenson 11y agoI imagine it's showing in benchmarks that approximate mobile usage might actually influence that timeline slightly.
- matsur 11y agoBuilding and linking your own sqlite.c is completely feasible. (We do it).
- DrJokepu 11y agoYes, that's something that I do as well, but will Core Data pick it up, or will it use the one that ships with the system?
- toyg 11y agosqlite existed before iOS and I doubt that platform is its "#1 use case". Sqlite powers tons of apps on many different platforms.
- kbenson 11y agoActually, I wouldn't be surprised at all, given that it seems to be the canonical on-disk data store for both Android and iOS. Whether you want to measure total uses by developers, the developers themselves, the users using it, the apps using it, the size data stored, I imagine mobile use probably swamps everything else. For example, I probably have 20-30 sqlite using apps on my phone, if not more. And I wiped it clean when I got a new phone a few months back and have been fairly hesitant to add apps I haven't found proven use for.
- simonh 11y agoThe post you're replying to doesn't mention iOS, just mobile (though it may have been edited). Anyway SQLite is heavily used in Android and well, and other mobile platforms going back to all but the early versions of Symbian even. It's been the standard for address book, calendar, settings, to-do apps, etc for over a decade. A typical smartphone probably contains around half a dozen SQLite DBs right out of the box, which means the building I'm in probably houses several thousand of them. The total number of DBs in the world must be in the tens of billions, just from mobile devices.
- toyg 11y agoparent says "Core Data", hence iOS. iOS alone is certainly not the #1 case, since Android has bigger numbers as you know. I just thought it smacked a bit of "Apple bubble", that's all.
- developernotes 11y ago>I'd like to see the same speed measurements done for mobile. While not exactly what you are looking for I can understand the sentiment. SQLCipher provides a project to time the performance of queries when run against standard SQLite vs. SQLCipher on iOS. https://github.com/sqlcipher/SQLCipherSpeed https://github.com/sqlcipher/SQLCipherSpeed
- shocks 11y agoRelevant change log entry: > Miscellaneous micro-optimizations result in 22.3% more work for the same number of CPU cycles relative to the previous release. SQLite now runs twice as fast as version 3.8.0 and three times as fast as version 3.3.9. (Measured using cachegrind on the speedtest1.c workload on Ubuntu 14.04 x64 with gcc 4.8.2 and -Os. Your performance may vary.)
- eblume 11y agoSQLite is amazing. It is a tech that no programmer should be unfamiliar with. It takes at most a day or two of playing with it to learn all of its various quirks and tricks, is easy to bake in to any project regardless of language, and performs like mad.
- daheza 11y agoAny particular resources you recommend to learn it?
- asveikau 11y agosqlite.org's documentation for the C api [1] and its explanation of what kind of SQL it supports [2] are both very good. [1] http://sqlite.org/cintro.html http://sqlite.org/cintro.html [2] http://sqlite.org/lang.html http://sqlite.org/lang.html
- agumonkey 11y agoIf I may, python sqlite interface is pretty great too. https://docs.python.org/3.4/library/sqlite3.html#sqlite-and-python-types https://docs.python.org/3.4/library/sqlite3.html#sqlite-and-...
- keyneus 11y agoThe sqlite3 module that comes with python is terrible when it comes to errors. For example, if OperationalError is raised, it could be any of the following SQLite errors: SQLITE_ERROR, SQLITE_PERM, SQLITE_ABORT, SQLITE_BUSY, SQLITE_LOCKED, SQLITE_READONLY, SQLITE_INTERRUPT, SQLITE_IOERR, SQLITE_FULL, SQLITE_CANTOPEN, SQLITE_PROTOCOL, SQLITE_EMPTY, SQLITE_SCHEMA How do you know which? The only thing you can do is convert the exception to a string and compare error messages. That's absurd. How about IntegrityError? That's either SQLITE_CONSTRAINT or SQLITE_MISMATCH. Which one? Compare strings again. If you need to use SQLite with python, apsw actually converts errors to discrete exceptions.
- PythonicAlpha 11y agoSQLite is one of the rare examples of very elaborate implemented software that is very good maintained, too. One of the best pieces of free software around. Where else do you find software, that was very good implemented from the beginning and is so carefully enhanced and optimized? (And not only for new "features") -- I remember no official available example.
- arandomdude 11y agoAnd SQLite is also one of the most tested pieces of software in the world: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
- huu 11y agoSQLite is the ultimate blackbox. Reliability beyond compare
- fiveoak 11y agoA little off topic, but is there any benchmarking software to compare embedded databases? For instance SQLite vs H2.
- jmnicolas 11y agoYou would think with such a performances boost they would have put it first in the change-log, but no it's hidden at the end. One thing for sure, they never worked for AMD or Nvidia marketing department ;-)
- harperlee 11y agoSQLite should be the default way someone manages information on a binary data file, as opposed to just inventing your format from scratch. I remember on uni when we started defining our own formats, to later on learn about what goes inside a DBMS, and thinking, "Why on earth would I ever do the first thing again?" (not that there's no space for it, but it's not so big). Also, when I've found a couple of times that a file that I thought had a proprietary binary format was a SQLite file, it was bliss. So much easier to navigate, inspect and work with that a huge XML file or a plain text one.
- batou 11y agoPretty much everything I've built that is standalone uses exactly this model. It's a wonderful bit of tech!
- batbomb 11y agoI think this is somewhat incomplete information, and I say this as a SQLite lover. It depends on your type of data, but both HDF5 and SQLite are more than adequate formats for data, and you should be using one of them. I think HDF5 is actually a better format for long term data management, but I think SQLite is a easier to use in everyday life. There's also Parquet and Avro for these as well. High-Energy physics also has the "code is the documentation" clusterfuck known as ROOT files.
- rch 11y agoDo you have experience with parallel HDF5? It looks like it has been improving a lot as of late, but I can't really test it myself at present.
- chacham15 11y agoThis again misses the point. The point is that you should use the right tool for the job. If your application needs a particular format for whatever reason, use that format. For example, could you imagine trying to encode a video file in a sqlite database? Similarly, would you use a sqlite file for a plaintext document (like c++) or a binary executable? To give a more custom example: I was working on a project which tries to build a database of solutions to games (like Connect 4). Connect 4 has such a large search state that using a database is terrible because it doesnt meet the requirements of really fast lookups (with great caching), small size, and fast parallel writing that the solver needs.
- coleifer 11y agoI wrote a post a while back singing the praises of SQLite. Some of you may be interested: http://charlesleifer.com/blog/sqlite-small-fast-reliable-choose-any-three-/ http://charlesleifer.com/blog/sqlite-small-fast-reliable-cho...
- joshbaptiste 11y agoGreat, write up convinced me to use Sqlite instead of Mysql for a simple in-house webpage.
- wxs 11y agoWe use SQLite extensively in Minuum and I couldn't be happier. It's in all our critical real-time code paths that bring up candidates while you're typing. Because we're on SQLite our predictions keep getting faster for free! Faster predictions mean we can handle more types of errors (more missed characters, more complex typos) in the same time budget. Thanks SQLite, you guys are awesome.
- NelsonMinar 11y agoOne of the great things about SQLite is its incredibly comprehensive automated test suite. That allows aggressive optimization without fear you're breaking something.
- gtrubetskoy 11y agoWhen I was tinkering with http://thredis.org http://thredis.org I spent a lot of time looking into SQLite internals, and it is by far the most polished, comment-rich code base that I have ever seen, it reads like a fun book. Especially if you're interested in low-lever relational db stuff - how SQL statements are processed, data is stored, B-Trees, etc. It's better than any book out there (and such books tend to be very expensive).
- billK 11y agoThe change in title hides the main point (~2x speed increase) and makes it appear as a simple bugfix release.
- coleifer 11y agoAnybody heard anything about SQLite4?
- coleifer 11y agoI wrote up a quick blog post describing how to build the new FTS5 search extension, which was included in the 3.8.11.1 release: http://charlesleifer.com/blog/building-the-sqlite-fts5-search-extension/ http://charlesleifer.com/blog/building-the-sqlite-fts5-searc...