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 );
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
|