How do I use tablename placeholders in a prepared statement?

I need some Perl help in tweaking this code to optimize the use of this DBI prepare statement. I'm sure I can optimize/speed this up more. I think I've got this set up correctly where I have one connection, but I want to make sure I'm parsing the query once and just substituting in the only (1) parameter change. I'm just not sure how to arrange that.

I am basically taking a list of tables and looping the tables as input for a single query. Basically, this is the same qu开发者_如何学Cery with a different table name.

Can someone show me how to I can optimize this?

Here is my code in progress:

my @tbls = qx(mysql -u foo -pf00 --database $dbsrc -h $node --port 3306 -ss -e "show tables");
my $dbh = DBI->connect("DBI:mysql:database=$dbsrc;host=$node;port=3306",'foo','f00');

# Creating a logfile
open (MYLOG, ">$dmpdir$node-mytstdmp-$dt.log") || die "cannot append";

# Loop through each table and create its own data file
foreach my $tbls (@tbls)
{
   chomp $tbls;
   print MYLOG "START Time ==> ", &dt2, "\n";
   extract_data($dbh, $tbls);
   print MYLOG "TIME END  ==> ", &dt2, "\n";
};
$dbh->disconnect;
close (MYLOG);

sub extract_data
{
     my($dbh, $tbls) = @_;
     my $out_file = "$dmpdir$node-$tbls.$dt.out";
     open (my $gzip_fh, "| /bin/gzip -c > $out_file.gz") or die "error starting gzip $!";
     print MYLOG "Creating dmp file ==> $out_file.gz\n";
     my $sth = $dbh->prepare("SELECT UUID(), '$node', 1, 2, flg, upd, vts FROM $tbls");
     $sth->execute();
     while (my($uid, $hostnm,$1,$2,$flg,$upd,$vts) = $sth->fetchrow_array() ) {
       print $gzip_fh "_key_$uid^Ehost^A$hostnm^E1^A$1^E2^A$2^Eflg^A$flg^Eupd^A$upd^Evts^A$vts^D";
     }
     $sth->finish;
     close $gzip_fh or die "Failed to close file: $!";
};

A table name cannot be used as a parameter to execute. This is documented in perldoc DBI:

With most drivers, placeholders can't be used for any element of a statement
that would prevent the database server from validating the statement and
creating a query execution plan for it. For example:

  "SELECT name, age FROM ?"         # wrong (will probably fail)
  "SELECT name, ?   FROM people"    # wrong (but may not 'fail')

BTW, if you want to optimize this code, you should start with replacing the call to mysql with a call to DBI:

my @tbls = @{ $dbh->selectcol_arrayref('SHOW TABLES') };

Run your code through a profiler such as NYTProf. This will tell you where you're spending all your time in your program.

But really, it looks fine to me. Coding problems aside (you're using typeglobs for filehandles, you're using the two-argument form of open(), you're not using placeholders in your queries) I don't think there's much more you can do from a performance issue. But then, you need to do proper profiling to determine that.

Generally speaking fetchrow_arrayref is quicker than fetchrow_array as it reuses the array. If combined with bound columns it is usually even faster but read the DBI docs for fetchrow_arrayref as you have to be careful not to store references to the returned data (which you are not doing). fetchall_arrayref might also be quicker than what your doing here but that largely depends on how many rows your select returns.