Sign In/My Account | View Cart  

advertisement

AddThis Social Bookmark Button

Perl Program Repair Shop and Red Flags
Pages: 1, 2

Placeholders

The next obvious target is the VALUES section. There are two problems here. First, it's too long and there are too many repeated references to one variable; It would be better to abbreviate if we could. And second, if any of the @data items contains an apostrophe, the code will break. We can solve the second problem by using the DBI module's "placeholder" feature; this incidentally provides a simple solution to the first problem in the form of an array slice:



$sth = $dbh->prepare("SELECT * FROM info");
$sth->execute();
while (my @data = $sth->fetchrow_array) {
  $SqlStatement4 = ("INSERT INTO Customer_Information " .
"(CustomerNumber, Name, Address1, Address2, City, State, 
  ZipCode, Email, DefaultPassword) " .
 "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "); 
  my $sth4 = $dbh->prepare($SqlStatement4);
  $sth4->execute(@data[1..4, 6..9, 16]);
}

Loop hoisting

There's no need to prepare the statement every time through the loop, because it never changes. (This is another benefit of the placeholder approach.) So we can get a performance win by hoisting the prepare out of the loop;

$sth = $dbh->prepare("SELECT * FROM info");
$sth->execute();
$SqlStatement4 = ("INSERT INTO Customer_Information " .
"(CustomerNumber, Name, Address1, Address2, City, State, 
  ZipCode, Email, DefaultPassword) " .
 "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "); 
my $sth4 = $dbh->prepare($SqlStatement4);

while (my @data = $sth->fetchrow_array) {
  $sth4->execute(@data[1..4, 6..9, 16]);
}

This has the pleasant side-effect of turning the main loop of the routine from something long and complicated into something compact and simple.

The dot operator

Now let's clean up the SQL statement itself. Here we see another red flag: The dot operator. Yes, the dot operator is a red flag! It's rarely necessary, and usually a mistake. Here we can do better by writing the SQL like this:

$sth = $dbh->prepare("SELECT * FROM info");
$sth->execute();

$SqlStatement4 = 
  "INSERT INTO Customer_Information
       (CustomerNumber, Name, Address1, Address2, 
        City, State, ZipCode, Email, DefaultPassword)
   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  ";
my $sth4 = $dbh->prepare($SqlStatement4);

while (my @data = $sth->fetchrow_array) {
  $sth4->execute(@data[1..4, 6..9, 16]);
}

We've just eliminated eight punctuation characters that were cluttering up the SQL. Now another programmer looking at the string can see it instantly without having to filter out a bunch of superfluous periods and quotation marks. (Note that I also got rid of two superstitious parentheses. Perl has too much punctuation, so it's good to take advantage of opportunities to reduce it.)

Omit needless variables

Finally, the variable $SqlStatement4 is useless. It's only used once, and its name has no documentary value, so we might as well get rid of it:

$sth = $dbh->prepare("SELECT * FROM info");
$sth->execute();

my $sth4 = $dbh->prepare(qq{
   INSERT INTO Customer_Information
       (CustomerNumber, Name, Address1, Address2, 
        City, State, ZipCode, Email, DefaultPassword)
   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  });

while (my @data = $sth->fetchrow_array) {
  $sth4->execute(@data[1..4, 6..9, 16]);
}

There are more improvements to be made here. For example, now that the code is cleaned up, it's easy to see that it can simply be replaced with a single INSERT statement. But this is enough for now.

The original code was 815 characters long, and the result is 451 characters, a 45 percent reduction. Programmers have learned to distrust length measurements like this -- so much so that they often automatically reply "But you can reduce the length of any program by squeezing out the whitespace." But here we've made the program shorter while increasing the amount of whitespace. This is not unusual.

For more information

If you found this interesting, you will probably like the class. You may also enjoy the following www.perl.com articles, which represent some of my earlier thinking on this topic.

You may also want to peek at http://www.plover.com/~mjd/misc/flags/ which has a few samples of the slides that I am working on for the class.

Mark-Jason Dominus has been programming in Perl since 1993 and is the former managing editor of www.perl.com.

Return to the O'Reilly Network.




-->