If it has to be a dynamic list, make the proc take a single VARCHAR that contains a comma-separated list of tables.
If it can be static (PLEASE tell me it can!), hard-code the DROPs in a proc. Irregardless, start looking for a new job someplace where you don't have to do ridiculous things like this. It's better to leave BEFORE bankruptcy and the lawsuits. On Thu, Mar 3, 2011 at 08:25, Roode, Eric <ero...@barrack.com> wrote: > 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. > > -- pDale Campbell
