Howdy,

I am just starting to refactor a moderate-sized CGI site (perhaps 80 scripts, typically 100-300 loc). A lot of the scripts use SQL to retrieve data via DBI. The actual SQL is typically stored in each individual CGI script. As you can imagine, certain queries are very common, and could easily be factored into a StandardQueries module.

My thinking is to replace a code fragment like this:

my $sql_get_username = '
SELECT  USER_NAME
FROM    USERS
WHERE   USER_ID = ?';

my $sth_get_username = $dbh->prepare($sql);

[download]

With one like this instead:

use StandardQueries;
my $sth = $dbh->prepare(StandardQueries::get_username);

[download]

I have three questions about this approach:

  1. If StandardQueries gets large (say 100s of queries), what effect will this have on script performance?
  2. I was not intending on exporting anything from StandardQueries: am I right in thinking this will avoid unnecessary overhead?
  3. Are there any other ways of refactoring SQL-heavy code like this? In other words: is this approach reasonable, or are there pitfalls or better methods I didn't mention here?

Any thoughts much appreciated!

In reply to Impact of module size by Anonymous Monk

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.