Here's a thought: write a stored procedure that takes twenty (or fifty, or a hundred) varchar arguments, each the name of a table. Then it constructs and executes a dynamic "DROP TABLE foo" statement for each.
At least that would reduce the network traffic, and keep more of the execution in the database engine. Have you tried timing doing all of the deletes directly on the database by hand? Perhaps Perl+DBI+network is not your problem; perhaps it's just going to take a very long time to delete all those tables. -Eric -----Original Message----- From: Dan Cutler [mailto:dcut...@dsm.net] Sent: Wednesday, March 02, 2011 3:22 PM To: dbi-users@perl.org Subject: Suggest an approach for multi-statements? 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.
