4 ms·
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 b
by bane 14y ago
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.
- techtalsky 14y agoGreat 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.
- bane 14y agoOne 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.
- genwin 14y agoNice! As a longtime developer using a "proper RDBMS" I was amazed at how feature packed and speedy SQLite is for having such a small footprint.
- Argorak 14y agoWell, SQLite might not be rocket science, but it is related to it: http://en.wikipedia.org/wiki/SQLite#History http://en.wikipedia.org/wiki/SQLite#History (SQLite was created for software on missile destroyers)
- baudehlo 14y agoDBD::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.
- bane 14y ago(once again why HN is awesome) Thank you thank you thank you thank you from the bottom of my heart. 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.