«

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($_);
}

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

"Gregory Toomey" <> ¦b¶l¥ó news:2311981.hecxWES94K@gregs-web-hosting-and-pickle-farming ¤¤¼¶¼g...
> 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)
>

i got such error

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


> Make sure Name2 is indexed.
>
> gtoomey

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

>
> "Gregory Toomey" <> ¦b¶l¥ó
> news:2311981.hecxWES94K@gregs-web-hosting-and-pickle-farming ¤¤¼¶¼g...
>> 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)
>>
>
> i got such error
>
> DBD::XBase::db prepare failed: Relation expected near ` IN (select NAME1
> from table1)'
>
>
>> Make sure Name2 is indexed.
>>
>> gtoomey

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

"Gregory Toomey" <> ¦b¶l¥ó news:1224330.2GIZYlYOqS@gregs-web-hosting-and-pickle-farming ¤¤¼¶¼g...
> It was a dark and stormy night, and Johnson Lau managed to scribble:
>
> >
> > "Gregory Toomey" <> ¦b¶l¥ó
> > news:2311981.hecxWES94K@gregs-web-hosting-and-pickle-farming ¤¤¼¶¼g...
> >> 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)
> >>
> >
> > i got such error
> >
> > DBD::XBase::db prepare failed: Relation expected near ` IN (select NAME1
> > from table1)'
> >
> >
> >> Make sure Name2 is indexed.
> >>
> >> gtoomey
>
> 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

>
> gtoomey

On Mon, 06 Oct 2003 13:19:29 +0800, Johnson Lau wrote:
> #!/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?


--
Tore Aursand <>

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

"Johnson Lau" <> wrote in
news:blqu12$heq$:

> 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/AwUBP4FY4GPeouIeTNHoEQISzACg0GM9ilRlkUN4lBbMjVEhet eDc5sAn0qC
A9QbuAw7S73xHKkx7Yc1VTau
=j0A/
-----END PGP SIGNATURE-----

Tore Aursand <> wrote in
news:

> On Mon, 06 Oct 2003 13:19:29 +0800, Johnson Lau wrote:
>> #!/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.

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.