Consider a table t that has three columns, a, b and c, any of which may be null.

You have some long-running code that every now and then, inserts a record into the table where all or some of the columns are referenced:

my $sth = $db->prepare_cached( <<SQL ) or die $db->errstr;
insert into t (a, c) values (?, ?)
SQL
$sth->execute( $foo, $bar );

# ...
# sometime later
# ...

my $sth = $db->prepare_cached( <<SQL ) or die $db->errstr;
insert into t (c) values (?)
SQL
$sth->execute( $quux );

[download]

The first time DBI encounters an a-b-c tuple, it will parse the statement and generate an execution plan.

The subsequent times that DBI encounters an insert tuple it has already seen, it will skip the compilation and return the prepared statement immediately, with the attendant speed gains.

If you code could insert all possible single columns, pairs of columns and all three columns, you would wind with 3 + 3*2 + 1 = 10 3 + 3 + 1 = 7 cached insert statements.

Assuming I've understood the question you're asking.

In real life, the above scenario might occur when dealing with a hash, and depending on the keys that exist, you want to insert their values into columns of a table. Sometimes, (some of)? the keys are present, sometimes not.

- another intruder with the mooring in the heart of the Perl

In reply to Re: Clarification of variable placeholders v's prepare_cached by grinder
in thread Clarification of variable placeholders v's prepare_cached by macPerl

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.