Your choice of datastore should be orthogonal to the rest of your application. Design so that it can be changed. If YAML or XML does the job now, use it. But plan to be flexible.
I just completed a project where I wound up switching between five different serialization methods to get the combination of reliability and speed we needed with the sort of data structure we were using. Fortunately, I designed with this in mind and it took only a few minutes to make each change as we tested the different back-ends. If you are wondering, I didn't use or even evaluate SQLite for this project, because it wasn't a good fit.
On other projects I have had very good luck with SQLite. I ran into speed issues a couple of times, but I was able to resolve each one. I'll breifly describe each one, because it might be useful to you or someone else.
When I had huge 100+ MB files in SQLite, I did have some slowness, but I added appropriate indexes, and the problem vanished.
Another speed issue I had was when I was making many inserts. I solved the issue by wrapping the inserts in a transaction, so that the on-disk database only had to be updated once.
I also had to refactor some code that was trying to do the RDBMS' work. Behind the scenes, the code was making boatloads (thousands) of single row select statements, and then using that data from these to do another series of selects (hundreds per initial select) - fear the power of the dreaded cross-product! I replaced all that crud with some slightly more complex queries that got the data all in one go, and saw a vast speedup. The application went from LOCKED UP ALL THE TIME to snappy.
SQLite is a widely used RDBMS, I have no problem using it in production systems when it is appropriate.
The main thing is to remember that there are a variety of SQL databases (MySQL, Firebird, SQLite, PostgreSQL, blah blah blah) out there that can do searches, sorts, and joins for you. Used appropriately, they can save lots of hackery on your part.
|