Hi all!
I am seeking some advice.
I need to be able to drop several thousand (more like .25 million) tables from
a database using DBI.
I've got a simple script that works but it is quite slow...
The SLOW typical version:
while (my @rows = $sth->fetchrow_array) { # Get the list of tables to drop
from the a simple SQL query.
my ($tablename) = @rows;
$dbh->do("drop table $db.$tablename") or die $sql->errstr;
}
My next attempt:
$dbh->do("drop table1; drop table2; ... drop tableN;")
-- fails! Apparently I can't do multi-statements ??
My next attempt:
$sth = $dbh->prepare("DROP TABLE ?") or die $sth->errstr;
$sth->bind_param_array(1,\@list_of_tables_to_drop);
my $result = $sth->execute_array({}) or die $sth->errstr;
Also fails! I've tried many variations of the execute_array function (with and
without bind_param_array()), with and without { ArrayTupleStatus => \my
@tuple_status }). Maybe I just don't really get how to specify the params? -
Tried Google + documentation - I'm still confused!
The real question (before I try to determine my code issue):
What is the best typical approach when trying to get non-select statements to
run faster? Am I going down the right path?
Thanks all!
--Dan
BTW, DBI version is 1.615 using DBD::Teradata driver.
