One thing you can do to speed up your current program is to simply pass a
reference instead of a value. Try this instead.
my $tablename = $sth->bind_col(1, my \$tablename);
While($sth->fetch){
$dbh->do("drop table $db.$tablename") || die $sth->errstr;
}
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Bill
Ward
Sent: Wednesday, March 02, 2011 1:28 PM
To: Dan Cutler
Cc: [email protected]
Subject: Re: Suggest an approach for multi-statements?
You don't say what database it is, but I'd be tempted to generate a .sql
file and run it through the database's CLI command instead of using DBI.
On Wed, Mar 2, 2011 at 12:22 PM, Dan Cutler <[email protected]> wrote:
> 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.
>
--
Check out my LEGO blog at http://www.brickpile.com/
View my photos at http://flickr.com/photos/billward/
Follow me at http://twitter.com/williamward
