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.


TGI says moo

In reply to Re^5: RFC: RPG ;-) by TGI
in thread RFC: RPG ;-) by pobocks

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.