Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
Hi Matt,

tilly referred me to SQLite in a couple of threads I've posted (Performance quandary and Berkeley DB performance, profiling, and degradation...) regarding a BerkeleyDB based program I'm developing...

So, in the spirit of desparately trying stuff until something works so that I can stop working on this darned program once and for all, I've just converted it from using BerkeleyDB to DBI::SQLite, and it seems to be working great. So the answer to your question is, "Yep. I would use SQLite." It seems to be working quite nicely, except for one quirk that I think is likely due to my ignorance of the DBI (more on that in a moment). The test database is about a quarter million sub-4k objects, with the real database topping 2 million--I haven't run it on the big database but on the test db it works great! The old BerkeleyDB version was roughly equal or a little faster in the case of a few thousand objects, but performance declined rapidly due to some pathological handling code in my program (which using a relational db allows me to eliminate completely).

Anyway, it seems like an ideal solution for me, as I have to provide a simple to use set of RPMs for my programs and SQLite is a lot easier to package up and use than MySQL or PostGreSQL. I'll be spending the next few hours on converting the support tools and testing with my small db before rolling it out to a client site for more serious work (don't worry, I can keep an eye on things and deal with any bugs that pop up--I know it's early code). Anything I ought to be watching for? In a couple of posts, here and at use.perl, I think you've mentioned suspected memory leaks--still suspicious?

Now to my problem...I have the following line:

$dbh->do("INSERT INTO objects (md5, url) VALUES ('$md5', '$key')");

Which produces the following error:

DBD::SQLite::db do failed: near "'http://www.gnome.org/images/banner-c +ontribute": syntax error at ./logtaild.pl line 113, <OBJECT> line 1.
But only on a few (very few) entries into the db. I got about 50 errors like this during the full 250,000 object insertion run. The md5 field is a CHAR(32) while the url field is a VARCHAR(8192)...Am I doing something wrong here? I don't quite know what to make of the extra "'" in the entries that lead to an error--there is nothing particularly different about these few entries that I can see. Searching the Monastary and Google doesn't bring me any enlightenment, so it doesn't seem to be a DBI general issue, but I may have missed it.

Anyway, thanks for the SQLite DBD--it's very cool, and worked right out of the box. One issue, it doesn't seem that the NDEBUG option is disabled in the module build...the SQLite author pretty strongly recommends doing so, at least when performance matters.


In reply to Re: Would you use SQLite? by SwellJoe
in thread Would you use SQLite? by Matts

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!
  • 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, 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, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            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.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others meditating upon the Monastery: (6)
    As of 2014-12-20 08:54 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      Is guessing a good strategy for surviving in the IT business?





      Results (95 votes), past polls