15 ms·
SQLite 3.8.7 is 50% faster than 3.7.17
- easytiger 12y agoI'm on 3.8.6. So will have most of the benefits. The temptation with sqlite is that it is so well maintained to only do cursory testing with it, so might drop this in. Interestingly: > A full 10% of the performance gain has come since the previous release. >There have been a lot of changes.
- Shish2k 12y agoI wonder what most of those optimisations were? In particular, are there any that are general enough to be worth knowing about, or even worth including at the compiler level?
- easytiger 12y agoEverthing detailed: http://www.sqlite.org/changes.html http://www.sqlite.org/changes.html
- rlpb 12y agoHow is this work funded? Are these developers working full time on SQLite, or is this work done in developers' spare time?
- cowsandmilk 12y agonot going to say it is 100% of their time, but they are paid to work on it. You can see five consortium members on the front page of sqlite, the cost of a consortium membership appears to $75,000/year[1]. That's enough to pay developers. [1] http://sqlite.org/consortium_agreement-20071201.html http://sqlite.org/consortium_agreement-20071201.html
- ghshephard 12y agoAnd that doesn't even include Apple, who I have to believe has a big investment in sqlite, and probably has some form of support arrangement with them.
- yincrash 12y agoThe full price list. [1] [1] http://www.hwaci.com/sw/sqlite/prosupport.html http://www.hwaci.com/sw/sqlite/prosupport.html
- dagw 12y agoThe main SQLite developers have a company that has a few revenue streams. Mainly they offers support and maintenance contracts. There are also a couple of closed source extensions for SQLite for handling compressed and encrypted databases that they sell. Finally the complete test suite is closed source, so if you want to make closed source changes to SQLite and still make sure you don't break compatibility then you probably want to license that.
- qwerta 12y agoI think people under-estimate incremental improvements a lot. My tai-chi teacher told me that eastern culture is more about "putting one sheet of paper on another", there is slow and steady improvement. Western is much more about "big bang approach", make it or break it. SQLite has been around for 15 years, they never shifted their purpose and were constantly improving. You get almost perfect product at this point. I work on in-memory db which was evolving similar way. Now after more than decade of small improvements, it is faster than java collections, despite doing serialization and its own memory management.
- thomasahle 12y agoI'm not sure I understand the tradeoff. Given two tasks requiring an equal amount of work, are you saying that it's better to spend it on a 5% improvement than a 150% improvement? Sure, if all you have available are lots of 5% improvements, they'll also eventually add up. But won't you prefer to work on the biggest improvement/cost tasks first? Perhaps it should be seen versus the "throw it all away and start over" tendency? Like in steady refactors vs. big rewrites.
- regularfry 12y agoHere's one way to look at it. Say you can either spend X amount of effort on a 50% improvement, or you can spend X/10 on each of 10 5% improvements. Sounds the same, right? It's not. If the smaller increases compound, you get a (1.05^10=) 63% increase out of the latter situation. Plus you don't have to wait for everything to be done to get visible improvements.
- emiljbs 12y ago>Say you can either spend X amount of effort on a 50% improvement, or you can spend X/10 on each of 10 5% improvements. Sounds the same, right? This is the same thing. The point is that you say "10 5% improvements of state S0" in that sentence and then later on you use 10 5% improvements of state S0, S1... S9. This doesn't make any sense, could you explain to me how this would work?
- TheLoneWolfling 12y agoAnd this is one of the problems with "optimize later". If you have a large project in Python, for example, far too often there isn't any one spot that has massive gains if you translate it to C. It's the death of a thousand cuts.
- easytiger 12y agoWhat are you talking about?
- to3m 12y ago(At a guess...) It's related, I think, to the standard piece of received wisdom, that you shouldn't optimize until some unspecified "later". This isn't a bad rule of thumb, but efficiency and convenience aren't always happy bedfellows, and, when coding, people tend to choose convenience. The result can be that when this later date arrives, and you go to make the code run more quickly, you're a bit stuck, because it would require changes more sweeping than can be accommodated with the resources available. But had performance been taken into account from the start, the changes would have been easy to make, or the whole thing might not even have been an issue. An example might be writing a compute-heavy program in python rather than C. Another standard piece of received wisdom is that once you get to optimize later, you'll just run the profiler and there'll be some hot spot due to poor choice of algorithm. So you optimize that bit and then you're done. But in practice this happens quite rarely; it's more common for the code to just be generally slow. A common reason it's generally slow is because performance wasn't considered during development, so the end result is the same. This is what the reference to translating into C relates to, I suspect - the idea that in your slow python program there's this one bit that can be turned into C and suddenly your program is super fast. (Of course, for many programs, python's speed is irrelevant. And any compute-heavy python program is likely only to be improved by translation into C - though your average python program would probably be much harder to make any quicker. Maybe that just goes to show that you actually have to think about this stuff on a case-by-case basis rather than relying on received wisdom. Anyway, I pick python only because it was mentioned.) Belief in the improve-algorithm-later theory usually causes people to dismiss micro-optimizations as pointless, because rather than get 0.1% here and 0.1% there you can just put it off until later and then get 25% all in one go. But this attitude actually makes it more likely that the code will paint itself into a corner, leaving micro-optimizations the only option. There isn't really any good quick easy solution to any of this, which is why I always liked it when computers got faster every year :(
- bsaul 12y agoHow long will we have to wait to get this version usable for our CoreData apps in ios ? Is it possible to include it manually into our app or are we dependent on ios releases ?
- michaelmior 12y agoIf you want to use it with CoreData, you might have to wait. But you can always use sqlite directly and link with the latest library. It might well be possible to specify a different library version you can use with CoreData, but I've never tried this.
- deleted 12y ago[deleted]
- ponytech 12y agoBy "50% faster" what they actually mean is this new version requires 50% less CPU cycles than the former for doing the same task. In real world I think you will not get your program to run "50% faster": disk io is often the bottleneck in the database area.
- qwerta 12y agoSQLIte mostly runs on slow CPU devices with relative fast SSDs. Reads are parallel, but writes are single threaded, so there is no advantage from multi core ARM CPUs.
- bane 12y agoThe in-memory mode is also the default for many APIs. You can basically just use it like a collection for your language of choice but use SQL for putting and fetching things from the collection.
- onli 12y agoSqlite mostly runs everywhere, especially on normal desktop PCs, including all those old ones with slow HDDs and relatively powerful CPUs. But I don't get the ARM CPU remark, did parent change his comment?
- loudmax 12y agoNot for the same database obviously, but SQLite databases are cheap. If your data can be split into different databases, you can pull off parallel writes.
- eterm 12y agoPercentages are multiplicative: 50% fewer cycles for the same work would be 100% faster. 50% more work for the same cycles would be 33% fewer CPU cycles for the same work.
- qznc 12y ago50% fewer cycles might also mean, not faster at all, but your CPU stalls/idles more.
- zamalek 12y agoI'm glad to see something like this. People often take the typical optimization mantra too far. The minute you mention optimization on most internet forums (especially StackOverflow) you get slammed for promoting "premature optimization." That's not to say premature optimization doesn't exist. Sometimes writing faster code is the difference between using a hash instead of a list. Sometimes it's spending an entire day optimizing a function that's called once. The former is performance aware, the later is premature. It seems as though most would have you believe that they both fall into the "premature" bucket. It's as though people are using it as an excuse for their personal ignorance and would rather continue to revel in this ignorance than expand what they know. As far as I am concerned these "unimportant tidbits" about performance are gifts that keep giving. /somewhat offtopic rant
- Retric 12y agoThere are few absolutes in coding. Some times optimizing a function that's called once is worth it. People often perceive software that takes a long time to load is slow even if everything else is fast. Though even just changing the loading screen can do a lot even if the actual time something takes to load does not change. PS: If all someone wants to do is rotate an image loading photoshop really is slow.
- grokys 12y agoYes, but it's unlikely that someone asking "is it faster to concatenate strings in C# using the + operator or StringBuilder" on StackOverflow is someone that is writing a low-level database engine like SQLite. In my (totally anecdotal) experience the people who are more concerned about performance are beginners, because that is something that they can grasp, and this is where the "don't prematurely optimize" people are coming from. Obviously someone who is writing a low-level database engine knows that performance matters, and likewise, is likely to be running benchmarks rather than asking vague questions on SO.
- zamalek 12y agoIndeed. However, keep in mind that as this beginner "ramps up" to the knowledge level of someone who is working on a SQL database he will need a source to find out where costs lie. He's done some profiling, finds an expensive method with massive amounts of string concats, heads to Google and all he can find is "don't prematurely optimize." You can supplement an answer with "don't prematurely optimize," but the presumption that premature optimization is occurring is a premature assumption. He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever. ~ Chinese Proverb I would work with beginners that are not fools, than experts who are.
- rbobby 12y ago> Small. Fast. Reliable. Choose any three. Nice tag line.
- notacoward 12y agoCute, but inaccurate. Small? Not quite. http://symas.com/mdb/microbench/ http://symas.com/mdb/microbench/ Fast? No again. http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html Note that LevelDB itself isn't exactly the speed king here. It's just the clearest example. To its credit, SQLite does fare well when it comes to reliability. https://www.usenix.org/system/files/conference/osdi14/osdi14-paper-pillai.pdf https://www.usenix.org/system/files/conference/osdi14/osdi14... So ... choose one? (Downvotes from those who despise facts in ... oops, already happened.)
- erikb 12y agoI disagree. You can be not the smallest and still be small, etc. Still I think people should not downvote because they disagree. Especially the links in that post are quite valuable!
- notacoward 12y agoYes, it can be small without being the smallest, but such a claim should at least require being smaller than average/median for the category. In that example, it had a much larger footprint than the majority. If not for the inclusion of Brobdingnagian BDB, it would have been the largest. If I want to claim I'm strong, it's not sufficient for there to be only one who is weaker. Among embeddable databases, SQLite has no real claim to be small or fast. Its strengths lie elsewhere.
- erikb 12y agoSeems reasonable. What is its strength if not being small?
- zaphar 12y ago
- kazinator 12y agoAh, the good old 100/100 rule: 100% of the execution time is somewhere in 100% of the code. Sometimes when you profile code you're dismayed to find that there are no clumps where the time is spent. It's everywhere. This function here takes 3%, this one 5%, that one another 3%, ... you either have to make some radical change, or else make all of these a little faster.
- underlines 12y agoIs it possible to compile this version of sqlite for ARM and replace it on an Android device to improve performance? Android mainly uses SQLite for internal stuff and allmost all Apps use it... As stated here: http://forum.xda-developers.com/android/software-hacking/sqlite-50-faster-performance-boost-t2898180 http://forum.xda-developers.com/android/software-hacking/sql...
- personZ 12y agoMost definitely. Download AOSP, overwrite ./external/sqlite/dist with the new version, and build libsqlite.so and deploy on a rooted device (note that Chromium, and the Chrome browser, use their own copy of sqlite). Having said that, while Android uses sqlite copiously, I doubt this would make a perceptible difference in usage, performance or battery life. It is a 50% improvement by itself, but I doubt sqlite3 usage is even measurable compared to everything else going on in the system.
- underlines 12y agoYeah, thanks. That was the thing I was looking for. About your doubts, I'd like to quote the sqlite improvement comment: "Each of these optimizations is unmeasurable on a real-world system (we have to use cachegrind to get repeatable run-times) but if you do enough of them, they add up." Therefore, I will build this library for my ROM, even though it maybe only gives a 0.02% performance boost, but constantly improving my ROM with those micro-optimizations will probably lead to a 50% boost one day :P
- c4pt0r 12y agovery good news for android developer
- nXqd 12y agoThis is lovely. I believe the most important thing that we learn about this is how to measure your effort correctly. If you do micro optimization, but you don't track it, people will say that " DON'T PREMATURE OPTIMIZATION "
- pranayairan 12y agocan we expect this version to be included in android L ??
- sitkack 12y agoJust ship it in your own binary.
- mrweasel 12y agoI really like SQLite and posts like this always makes me wonder: Could I run a webshop on SQLite, if I did it right and how many users could I potentially support? Of cause it's not that hard to deploy a MySQL or Postgresql server, but it's still pretty hard to beat SQLite in ease of deployment and management. Running a simply webshop could be a easy as having a database for inventory and one for orders. Even very large stores aren't going to have thousands or even hundreds of orders a minute, so I don't think that writes would be an issue and reads certainly isn't an issue.
- timClicks 12y agoDepends. For read-heavy sites, it's possible. If you have a relatively small working dataset, you can load everything into RAM and periodically sync it to disk. Dealing with multiple writing threads with SQLite is really annoying. If you need to scale beyond a single host to serve the workload, something else will probably be easier to manage.
- eli 12y agoYou certainly could, but it's not really built with concurrent reading and writing. http://www.sqlite.org/faq.html#q5 http://www.sqlite.org/faq.html#q5
- strcat 12y agoThat FAQ entry is out-of-date as it doesn't mention the write-ahead logging support. https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- 12y ago
- nnethercote 12y ago> We have achieved this by incorporating hundreds of micro-optimizations. Each micro-optimization might improve the performance by as little as 0.05%. If we get one that improves performance by 0.25%, that is considered a huge win. Each of these optimizations is unmeasurable on a real-world system (we have to use cachegrind to get repeatable run-times) but if you do enough of them, they add up. Interesting! That reminds me of my own experience in a different context (from https://blog.mozilla.org/nnethercote/2011/07/01/faster-javascript-parsing/ https://blog.mozilla.org/nnethercote/2011/07/01/faster-javas...): > Cachegrind [does] does event-based profiling, i.e. it counts instructions, memory accesses, etc, rather than time. When making a lot of very small improvements, noise variations often swamp the effects of the improvements, so being able to see that instruction counts are going down by 0.2% here, 0.3% there, is very helpful.
- amaranth 12y agoOf course since processors are out of order and superscalar reducing instructions isn't always a worthy goal either. You might end up with less instructions but less things happening in parallel. And then of course you have to balance this with the cache efficiency of having to look at less instructions to complete a task. Basically, optimization at this level is really hard.
- nnethercote 12y agoAnd yet, in practice, I have found that optimizing for instruction counts works really well. I've gotten way more mileage out of Cachegrind's instruction counts than I ever have out of its cache or branch prediction simulations.
- nkurz 12y agoI was tempted to respond after your first comment, but this followup provoked me to action. When you wrote your post several years ago, modelling with Cachegrind may still have been a defensible approach. But the divergence between its generic processor simulation and actual real world performance continues to diverge. As you note, this divergence first became very apparent with the cache and branch predictions. Currently, I'd argue that your time will almost always be better spent checking the CPU's built-in performance monitors rather than using Cachegrind. For the cases where Cachegrind used to be useful, 'perf' is a joy!
- tbrock 12y agoAnyone using newer versions of SQLite out of homebrew on OSX? If so are you having any trouble with conflicts of any sort with the system version? OSX heavily relies on SQLite in many places and I'm curious if it causes trouble to try and use both (and have it in your path).
- nkurz 12y agoDeep within another thread, I had a conversation with 'nnethercote' regarding Richard's use of Cachegrind to optimize performance. My position is that it is no longer a useful approach, as the actual behaviour of modern processors has diverged so much from Cachegrind's simple simulation. Instead, I think it is much more productive to use tools like 'perf' that use the CPU's built in performance counters to measure the actual performance. My conviction inspired me to repeat Richard's test to see how the numbers compared when run on a current Haswell i7-4770 processor. Here's what I got for the versions he compared when compiled with GCC 4.8.1 and using 'perf stat speedtest1 --size 5'[1] (sizes are for the sqlite.o lib only): Cachegrind per Richard: 3.8.7a -Os: 953,861,485 cycles 3.7.17 -Os: 1,432,835,574 cycles Haswell: 3.8.7 -O3: 469,956,519 cycles (870,767,673 instructions, 1,069,032 bytes) 3.8.7 -Os: 542,228,740 cycles (884,541,860 instructions, 648,360 bytes) 3.17.7 -O3: 651,680,545 cycles (1,320,867,830 instructions, 1,002,192 bytes) 3.17.7 -Os: 771,304,471 cycles (1,406,527,795 instructions, 605,976 bytes) One can make of the numbers what you will, but here are some conclusions I drew: While the improvement is about the same magnitude that Richard sees, the actual number of cycles is off by about a factor of 2. Despite having a larger binary, and thus theoretically worse instruction cache behavior, -O3 beats -Os by about 20% in speed in both cases, at a cost of 40% (400K/1MB) in size. 'perf record -F100000 speedtest1 --size 5; perf report' is a really slick and easy way to figure out where the program is spending it's time. If I were optimizing this, I remain fairly certain it would be more a more effective approach than using Cachegrind. [1] I had to comment out the "sqlite3_rtree_geometry_callback" line in speedtest1.c to get it to compile, which might affect my numbers slightly, but from the source comments I don't think it was actually being used.
- cliffbean 12y agoI find perf a very useful tool, and use it frequently. At the same time, I don't see how your data supports your conclusion that perf is more effective than cachegrind here. The absolute cycle count is of limited interest. The most important thing these tools do is tell programmers where to look, and cachegrind seems to have done a good job of that here. In my experience, perf and cachegrind are two tools in the toolbox. perf is stronger at optimizing for the particular CPU I'm on, and it runs faster. Cachegrind collects more detailed information, and while the model it uses isn't perfect for the CPU I'm on today, it's usually good enough to be useful, and it's good for optimizing things likely to matter on other CPUs too.