improve the efficiency

[Johnson Lau]

I wrote a code to compare the NAME1 in table1 an NAME2 in table2. If a NAME2 equals to any one of NAME1, delete the record
containing NAME2. Each of table1 and table2 is in Foxpro (*.dbf) format, containing around 6000 records and sorting according to
NAME1 or NAME2. It takes me around 2 hours to finish the job. Is it possible to improve the efficiency? Thanks very much.


#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
my $def = $dbh->prepare("delete from table2 where NAME2 = ?", {'MaxRows' => 1});
foreach (@$abc) {
$lc->execute($_);
}

 

[Gregory Toomey]

It was a dark and stormy night, and Johnson Lau managed to scribble:

I wrote a code to compare the NAME1 in table1 an NAME2 in table2. If a
NAME2 equals to any one of NAME1, delete the record containing NAME2. Each
of table1 and table2 is in Foxpro (*.dbf) format, containing around 6000
records and sorting according to NAME1 or NAME2. It takes me around 2
hours to finish the job. Is it possible to improve the efficiency? Thanks
very much.


#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
my $def = $dbh->prepare("delete from table2 where NAME2 = ?", {'MaxRows'
=> 1}); foreach (@$abc) {
$lc->execute($_);
}

Knowing nothing about foxpro, I suggest
delete from table2 where NAME2 in (select NAME1 from table1)

Make sure Name2 is indexed.

gtoomey

 

[Johnson Lau]

It was a dark and stormy night, and Johnson Lau managed to scribble:


Knowing nothing about foxpro, I suggest
delete from table2 where NAME2 in (select NAME1 from table1)

i got such error

DBD::XBase::db prepare failed: Relation expected near ` IN (select NAME1 from table1)'

 

[Gregory Toomey]

It was a dark and stormy night, and Johnson Lau managed to scribble:

i got such error

DBD::XBase::db prepare failed: Relation expected near ` IN (select NAME1
from table1)'

Then your SQL probably hoes not have sub-selects. Just make sure Name2 is indexed ie has a secondary/non-unique index.

A database like Oracle or even mysql would be ables to do this in a few seconds so I dont know why it takes you hours.

gtoomey

 

[Johnson Lau]

It was a dark and stormy night, and Johnson Lau managed to scribble:


Then your SQL probably hoes not have sub-selects. Just make sure Name2 is indexed ie has a secondary/non-unique index.

A database like Oracle or even mysql would be ables to do this in a few seconds so I dont know why it takes you hours.

then i will try to convert the database to mysql, thanks very much

 

[Tore Aursand]

#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
my $def = $dbh->prepare("delete from table2 where NAME2 = ?", {'MaxRows' => 1});
foreach (@$abc) {
$lc->execute($_);
}

Does this really take _two hours_ to finish? I've never used FoxPro
actually, but I didn't think that it was so slow? Doing the same in MySQL
would have taken just a few seconds, I guess.

You are completely sure that it's only this part of your script that takes
so long to execute? There aren't anything else we should know about? And
you've tried to print out some debugging info in that 'foreach' loop?

 

[Eric J. Roode]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I wrote a code to compare the NAME1 in table1 an NAME2 in table2. If a
NAME2 equals to any one of NAME1, delete the record containing NAME2.
Each of table1 and table2 is in Foxpro (*.dbf) format, containing
around 6000 records and sorting according to NAME1 or NAME2. It takes
me around 2 hours to finish the job. Is it possible to improve the
efficiency? Thanks very much.

The perl code seems reasonably efficient. Perhaps you could improve the
database selects, but you'll get a better response for that if you ask in a
database newsgroup, I'll bet.

- --
Eric
$_ = reverse sort $ /. r , qw p ekca lre uJ reh
ts p , map $ _. $ " , qw e p h tona e and print

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBP4FY4GPeouIeTNHoEQISzACg0GM9ilRlkUN4lBbMjVEheteDc5sAn0qC
A9QbuAw7S73xHKkx7Yc1VTau
=j0A/
-----END PGP SIGNATURE-----

 

[Eric Bohlman]

Does this really take _two hours_ to finish? I've never used FoxPro
actually, but I didn't think that it was so slow? Doing the same in
MySQL would have taken just a few seconds, I guess.

FoxPro doesn't really have native SQL support; DBD::XBase is providing it
through flat-file operations. It's not actually using an optimized
database engine.

If table2 isn't indexed (or DBD::XBase can't take advantage of an index)
then the algorithm reduces to a linear search of table2 for each entry in
table1, giving quadratic behavior. In that case, short of switching to a
true database engine, the OP would be best off reading in all the entries
in both tables, using standard hash methods to find their intersection, and
then doing deletes on only those keys in the intersection.