I am not actually reading all the data into Perl, just the smaller of two tables in an SQLite DB (one has 40000 records, the other 26 million). Profiling with Devel::NYTProf indicated that around 50% of the time was spent doing SELECTs on the database, so that looked like a good starting point for optimisation. As I have access to a cluster with available memory varying between 18-90 GB per node, I thought it might be worthwhile investigating whether some of the operations could be performed in memory, especially as I currently only require two such look-up tables.

The idea is that with the look-up tables I can create them once and thereby effectively store the results of multiple SELECTS, which can then be accessed via the hash key. This seemed like it might be faster in Perl, but as you point out, a single SELECT is what the DB is optimised to do. So I guess my mileage may vary.

Cheers,

loris

In reply to Re^2: Creating SELECT-like look-up tables as hashes by loris
in thread Creating SELECT-like look-up tables as hashes by loris

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.