Fun SQLite story, I had a project that needed to do reasonably large scale data processing (gigabytes of data) on a pretty bare boned machine in a pretty bare boned environment at a customer location. I had a fairly up-to-date perl install and notepad. For the process the data needed to look at any single element in the data and find elements similar to it. I thought long and hard about various complex data structures and caching bits to disk to make portions of the problem set fit into memory, and various ways of searching the data. It was okay if the process took a couple days to finish.
It suddenly hit me, I wonder if the sysadmins had installed DBD::SQLite...yes they had!
Why mess with all that crap if I could just use SQLite. Reasonably fast indexing, expressive searching tools, I could dump the data onto disk and use an in memory SQLite db at the same time, caching solved. It turned weeks of agonizing work (producing fragile code) and turned it into a quick 2 week coding romp. The project was a huge success and spread to a couple other departments.
One day they asked if I could advise a group to build a more "formal" implementation of the system as they were finding the results valuable. A half dozen developers and a year later they had succeeded in building a friendlier web interface running on a proper RDBMS (Oracle 10something) with some hadoop goodness running the processing stuff on 8 or 9 dedicated machines.
In the meantime, and largely because SQLite let me query into the data in more and more useful ways that I hadn't foreseen, I had extended the original prototype significantly and it was outperforming their larger scale solution on a 3 year old, very unimpressive, desktop with the same install of perl and sqlite. On top of the raw calculations, it was also performing some automated results analysis and spitting out ready to present artifacts that could be stuck in your presentation tool of choice. Guess which ones the users actually used?
Soon I had a users group, regular monthly releases and was running a semi-regular training class on this cooky command line tool (since most users had never seen a command line). I've since finished that job up and moved on, and last I heard my little script using SQLite was in heavy use, the enterprise dev team was still struggling getting the performance of their solution up to the levels of my original script, and hadn't even touched the enhancements yet.
SQLite was rock-iron solid the entire time. I had a few things I wished it could do, but I usually found a way to manage. I wasn't using it for its feature, it existing was the feature. SQLite sprinkled magic on my one-off hack job and turned into a thing that people ended up using.
DBD::SQLite remains one of my greatest successes - so glad you like it.
So many projects I have prototyped using SQLite (although for most I later upgrade to PostgreSQL). Such a great little tool, and building DBD::SQLite the way I did (including all the source) made it so easy for anyone to install.
DBD::SQLite has been one of the most important libraries for perl that I've ever used. Including the SQLite libraries instead of making it another dependency to install someplace makes it so unbelievably useful.
Plus it's surprisingly easy to move code over to using a more grown up database later.
I've had similar story - direct port of "sqlite" to "postgres" (localhost connection) made postgresql originally 6 times slower. But then I've made a connection pool connecting N, N2, or N3 connections where N is the number of logical CPU's. The trick was to use pg_export_snapshot, and make all connections to go through the same place.
This way I matched SQLite, but then I've made it x3-x4 faster by using COPY (SELECT *....) in BINARY format (took some time to write binary parser) - and what was loading for 3-4secs from SQLite, loads for 0.8secs from PostgreSQL 9.2 (Windows 7 / 32GB / 6 core / 12 cpu machine).
e.g. - the point is - that if you care, you could go and optimize a lot. This is not a fair story though, since I've used real enums, floats and ints on PostgreSQL which saved quite a lot of space. The db was bigger than SQLite even with that, but somehow faster... though faster only when used through multiple connections.
I could've go the opposite way, and find ways to make SQLite shine in this respect.
One lesson from the game development industry is that it takes 5 years to develop the best looking games at the end of the hardware cycle. And the hardware did not change even bit for the whole time.
It takes a lot of learning, experimenting, trying out strange things until you get good. And even then, someone comes and makes your solution look stupid :) (I hope someone comes and would do that for me, so I just sit, enjoy and smile at his/her code - several times I've seen it).
To add another war story: back in 2004 I was in the position of having to write software for a very, very underpowered barcode terminal running ucLinux (the intermec ck1, for those interested. Only a year later, it was discontinued (grrr) due to the EU finalizing the RoHS norm).
I needed to synchronize product data from our web application with that scanner in order to display useful info instead of just scanned bar codes.
Now, after 9 years, one user is still holding on to their device and we're still synchronizing product data to that SQLite database. We never had crashes or data losses (even though recovery would be trivial) and ever since then, every subsequent hardware solution we were using always relied on SQLite for storage.
I don't even want to start thinking about how much pain it would have been to write my own database for that slow ck1 monster. Seeing SQLite run on the device in my serial terminal was butch a lt of fun and a great relief as it just saved me countless hours of work.
Great story. I suspect there are about a million single user applications out there running oracle and paying huge license fees for db's under a million rows that would have been orders of magnitude easier to set up and faster using SQLite.
One thing to ponder about single user apps running on Oracle: many environments won't let the developer manhandle the database, so now they need to divert or hire a dedicated DBA person to configure and do the dB stuff. SQLite literally makes this entire problem go away.
I love to use SQLite for projects which process data locally and do not have concurrent writes. SQLite is faster than other database alternatives I tried. I guess one of the main reasons is that there's no context switch on every query. Data set sizes are usually tens of million keys and tens of gigabytes of data. Works like a charm. Btw. SQLite was over 10 times faster than some of the slowest SQLite servers I tried. I have 20+ projects using SQLite successfully. Other tested options were MS SQL, PostgreSQL, MySQL, MongoDB and CodernityDB. MongoDB performance isn't so great, when every commit must be persisted.
Using the Go language you can pretty easily get around the concurrent write issue, using channels. Data for concurrent users is written to the same channel. A routine that reads from the channel does all the writing to the SQLite database.
It suddenly hit me, I wonder if the sysadmins had installed DBD::SQLite...yes they had!
Why mess with all that crap if I could just use SQLite. Reasonably fast indexing, expressive searching tools, I could dump the data onto disk and use an in memory SQLite db at the same time, caching solved. It turned weeks of agonizing work (producing fragile code) and turned it into a quick 2 week coding romp. The project was a huge success and spread to a couple other departments.
One day they asked if I could advise a group to build a more "formal" implementation of the system as they were finding the results valuable. A half dozen developers and a year later they had succeeded in building a friendlier web interface running on a proper RDBMS (Oracle 10something) with some hadoop goodness running the processing stuff on 8 or 9 dedicated machines.
In the meantime, and largely because SQLite let me query into the data in more and more useful ways that I hadn't foreseen, I had extended the original prototype significantly and it was outperforming their larger scale solution on a 3 year old, very unimpressive, desktop with the same install of perl and sqlite. On top of the raw calculations, it was also performing some automated results analysis and spitting out ready to present artifacts that could be stuck in your presentation tool of choice. Guess which ones the users actually used?
Soon I had a users group, regular monthly releases and was running a semi-regular training class on this cooky command line tool (since most users had never seen a command line). I've since finished that job up and moved on, and last I heard my little script using SQLite was in heavy use, the enterprise dev team was still struggling getting the performance of their solution up to the levels of my original script, and hadn't even touched the enhancements yet.
SQLite was rock-iron solid the entire time. I had a few things I wished it could do, but I usually found a way to manage. I wasn't using it for its feature, it existing was the feature. SQLite sprinkled magic on my one-off hack job and turned into a thing that people ended up using.