I'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.
Different 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.
No 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.
I'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?
Actually, 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.
The 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.
parent 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.
>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.
> 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.)
SQLite 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.
The 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:
Hmm I liked the python types embedding and never really worried about dealing with errors that way that much but you're right that should be improved. Thanks for the suggestion.
(I am the APSW author). The reason why the sqlite3 module (aka pysqlite) behaves the way it does is because of DBAPI (Python standard database API). That tries to make all databases look and behave the same. pysqlite could do more - eg the SQLite error code could be an attribute on the exception, but the code base is very rarely updated.
Wanting a Python wrapper for SQLite that celebrated SQLite's features, functionality and semantics rather than paper them over is why I started APSW a decade ago. It also has considerably better testing and diagnostics, and is thread safe.
Thank you for your work on APSW, it's a fantastic library. I'm the author of the peewee ORM[1] and I've included an APSW backend[2] for folks who want to use peewee with APSW. Being able to write extension modules in Python is such a cool feature! I also made a demo that exposes Redis as a SQLite table[3].
If you’re not familiar with RDBMS, general introductions to SQL and relational algebra should be useful. Back in uni our textbook was Fundamentals of Database Systems[1]. (It’s okay, but there are probably better books out there.)
SQLite 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.
SQLite 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.
I 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.
This 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.
Chapter 11 of the Newton Programmers Guide[1] gives a fairly detailed description with pictures. Relational Databases are nice, but I would rather deal in objects (well frames).
I haven't found just a soup implementation (would be interesting for javascript given the prototype objects). NEWT/0[1] is a full NewtoScript but it looks unused.
We 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.
One of the great things about SQLite is its incredibly comprehensive automated test suite. That allows aggressive optimization without fear you're breaking something.
When I was tinkering with 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).
I 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?
53 comments
[ 2.8 ms ] story [ 108 ms ] threadFor 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.
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
> 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.)
[1] http://sqlite.org/cintro.html
[2] http://sqlite.org/lang.html
https://docs.python.org/3.4/library/sqlite3.html#sqlite-and-...
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.
Wanting a Python wrapper for SQLite that celebrated SQLite's features, functionality and semantics rather than paper them over is why I started APSW a decade ago. It also has considerably better testing and diagnostics, and is thread safe.
http://rogerbinns.github.io/apsw/pysqlite.html
[1] http://docs.peewee-orm.com/
[2] http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#a...
[3] https://github.com/coleifer/peewee/blob/master/examples/redi...
[1] http://www.pearsonhighered.com/educator/product/Fundamentals...
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.
One thing for sure, they never worked for AMD or Nvidia marketing department ;-)
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.
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.
"SQLite does not compete with client/server databases. SQLite competes with fopen()."
1) https://en.wikipedia.org/wiki/Soup_(Apple)
1) https://manuals.info.apple.com/MANUALS/1000/MA1510/en_US/New...
1) http://gnue.github.io/NEWT0/
http://charlesleifer.com/blog/sqlite-small-fast-reliable-cho...
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.
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?
http://charlesleifer.com/blog/building-the-sqlite-fts5-searc...